Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE » How To » Search for field in related table
Search for field in related table [message #675] Tue, 13 March 2007 12:19 Go to next message
janalwin is currently offline  janalwin
Messages: 17
Registered: May 2006
Location: Groningen
Junior Member
Hallo,

Is it possible to do a search on a field from a related table?

I have a table with product,brands, and products_brands. Some products are conversion sets for other brands. These relations are placed in product_brands.

If i search a brand, I also want to find the conversionsets for this brand.

This is a simplefied version what i've tried to do:
$sql_from= "product LEFT JOIN products_brands ON product.product_id=product_brands.product_id "; 
....
$where="brand_id='2'  OR products_brands.brand_id='2'"
$x=getData($where)



The product_brands.brands_id part seems to get lost in the _sqlAssembleWhere function of std.table.class.inc.

I've tried adding a fieldspec for products_brands.brand_id to the product table, but this didn't help.


Re: Search for field in related table [message #676 is a reply to message #675] Tue, 13 March 2007 13:20 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2350
Registered: April 2006
Location: Surrey, UK
Senior Member
The contents of $where ($sql_where) is filtered to remove any columns which do not exist in the current table. If you are joining to other tables then search criteria for these other tables can be placed inside $this->sql_search using code inside the _cm_pre_getData() method. The contents of $this->sql_search has a different filter mechanism.

Re: Search for field in related table [message #677 is a reply to message #675] Tue, 13 March 2007 16:26 Go to previous messageGo to next message
janalwin is currently offline  janalwin
Messages: 17
Registered: May 2006
Location: Groningen
Junior Member
Thanks for the speedy reply.

I'll try it later this week
Re: Search for field in related table [message #1763 is a reply to message #676] Wed, 22 October 2008 16:54 Go to previous messageGo to next message
bmoore is currently offline  bmoore
Messages: 1
Registered: October 2008
Junior Member
Is there anything that goes more in depth into this topic. I have been trying to search by a related table for quite some time.
Re: Search for field in related table [message #1764 is a reply to message #1763] Wed, 22 October 2008 17:07 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2350
Registered: April 2006
Location: Surrey, UK
Senior Member
First you need to identify the SQL statement that does what you want. Then you get the framework to generate that statement.

Re: Search for field in related table [message #1788 is a reply to message #676] Wed, 05 November 2008 03:23 Go to previous messageGo to next message
rhogan is currently offline  rhogan
Messages: 4
Registered: November 2008
Location: Wolfeboro, NH
Junior Member
AJM wrote on Tue, 13 March 2007 13:20

The contents of $where ($sql_where) is filtered to remove any columns which do not exist in the current table. If you are joining to other tables then search criteria for these other tables can be placed inside $this->sql_search using code inside the _cm_pre_getData() method. The contents of $this->sql_search has a different filter mechanism.



Could you provide a specific example of this? What code goes in which file?

For instance, I've got 2 tables with a one-to-many relationship, one person to many phone numbers.

I'd like to add phone number to the search screen for person in a drop down. So the user who has a phone number can find out what person it belongs to. In other words, I want to do a search on the senior table, based on a value in the junior table, and I need to have that field appear for input on the senior table search screen.

This seems like it should be simple, but I've been working through the tutorial for at least a week and I just can't find the solution. I can't have a user pick the person before getting to phone, because, that's what they would be searching for.

Thanks,
Boz

[Updated on: Wed, 05 November 2008 03:27]

Report message to a moderator

Re: Search for field in related table [message #1789 is a reply to message #1788] Wed, 05 November 2008 04:52 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2350
Registered: April 2006
Location: Surrey, UK
Senior Member
Look at my previous reply.
Quote:

First you need to identify the SQL statement that does what you want. Then you get the framework to generate that statement.

Take a look at FAQ08 and FAQ84 for details on how to achieve the latter.


Re: Search for field in related table [message #1889 is a reply to message #1788] Tue, 02 December 2008 11:40 Go to previous messageGo to next message
rhogan is currently offline  rhogan
Messages: 4
Registered: November 2008
Location: Wolfeboro, NH
Junior Member
rhogan wrote on Wed, 05 November 2008 03:23

AJM wrote on Tue, 13 March 2007 13:20

If you are joining to other tables then search criteria for these other tables can be placed inside $this->sql_search using code inside the _cm_pre_getData() method. The contents of $this->sql_search has a different filter mechanism.




Tony,

I've been trying this but the _cm_pre_getData() method doesn't seem to be getting called when the user presses the "Search" button.

Thanks,
Boz
Re: Search for field in related table [message #1891 is a reply to message #1889] Tue, 02 December 2008 12:37 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2350
Registered: April 2006
Location: Surrey, UK
Senior Member
When the user presses the SUBMIT button in the search screen any data entered is put into a string which is passed back to the calling task. When that task is re-activated this string is loaded into $this->sql_search of the relevant object so that it can be actioned during the next call to the getData() method. If you check with your debugger when the task is reactivated you will see that it does go through the getData() and _cm_pre_getData() methods.

Re: Search for field in related table [message #1892 is a reply to message #1891] Tue, 02 December 2008 12:45 Go to previous messageGo to next message
rhogan is currently offline  rhogan
Messages: 4
Registered: November 2008
Location: Wolfeboro, NH
Junior Member
I'm sorry. It's before it gets to the Submit. I'm trying to add a field to the search screen that isn't in the table, rather it's in a junior table. I've altered the SQL in the senior table class, the screen layout script and the component script, but still can't get the new/foriegn field to show up.
Re: Search for field in related table [message #1893 is a reply to message #1891] Tue, 02 December 2008 13:47 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2350
Registered: April 2006
Location: Surrey, UK
Senior Member
I have put an example of how to do this in FAQ120.

Re: Search for field in related table [message #1894 is a reply to message #1893] Tue, 02 December 2008 13:49 Go to previous message
rhogan is currently offline  rhogan
Messages: 4
Registered: November 2008
Location: Wolfeboro, NH
Junior Member
Awesome, thanks!
Previous Topic: Use a popup from a navigation button
Next Topic: Dynamic Report Column Headings
Goto Forum:
  


Current Time: Fri Jun 14 14:59:31 EDT 2024

Total time taken to generate the page: 0.01201 seconds