| Search for field in related table [message #675] |
Tue, 13 March 2007 12:19  |
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  |
AJM
Messages: 2347
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.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
|
|
| Re: Search for field in related table [message #1788 is a reply to message #676] |
Wed, 05 November 2008 03:23 |
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 #1891 is a reply to message #1889] |
Tue, 02 December 2008 12:37 |
AJM
Messages: 2347
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.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
