I was wondering if there is a way to join searches. My problem I am trying to solve is I have districts, counties, and zip codes.
Goal: I want to be able to filter the zip codes by district.
County: id, county_name, district_id
Zip Code: id, zip_code, county_id
As you can see these are one to many relationships and I can easily build a select statement to have a table that has zip_code, county_name, and district_id. Currently I can filter by county for the zip code and I can filter the county by district but cannot figure out how to filter the zip code by district since there is not a way I know of to join the search results like we can in a SQL query.
So I am taking the route of trying to use stages in the matching but am not getting the results I expected. My question is if I get all the counties from the district I specify in the 1st stage then how do I use the counties to display what zip codes are in the counties in the final stage. Assume that by selecting a county I can get the group of zip codes in that county.
I ended up copying the seblod_table template and adding some query to get the cck id of the items I needed (got value from a field I put in with no storage type) and then populated the items array myself using no pagination. Now to just add some code to make the table the way I want it. NOTE: Doing it this way allows you still use the filtering the way you normally would but at the end it further filters the results with your query.
If it is just a relationship between two fields there is no need to take this approach because you should have a field state_to_country which would store the value of the country and you can just use a Related Article / Dynamic - Select. Use a Dynamic Select with a Free Query if you don't want to store the country value as its id. But if you making relationships through the state and need to get the country then you run into the problem I faced above.
The problem I have is that there is no way to define fields in a content type through a complex select statement on pre-existing data (not a column of the #__cck_store_form_content_type). What I wanted to be able to do is import all of the zip codes, counties, and districts with importer and be able to define the existing relationships between them without having to define/store an extra column of district_id in zip_code. So I want a "Dynamic" field (would be similar to Dynamic Select which allows me to define query and joins) so that I can define/change the structure of the content type with already existing data and make it searchable in that content type. (In other words so I can find all zip codes in district 1 ... zip code -> county -> district)
Currently this is not possible with the way the code is written because given the storage table and column being filtered the following is done:
$sql->join('LEFT', 'table ON (pk=id) where table.column=value')
what I need:
$sql->leftjoin('(SELECT table.id AS id, storage_table.column AS column FROM ... the rest of my dynamic select)' AS new_table ON (pk=new_table.id) where new_table.column = value')
It doesn't seem too hard to do but probably requires changing core code and I didn't want to have to deal with redoing it every time I updated. Is there a way to make the plugin change the way the left join is done? I noticed that tick marks are used so you cannot hack it and change the storage table because a select statement is not a valid table name. Thanks in advance :)
Thank you for the answer but this still wont work. The point is that I have 3 tables linked together: table1 -> table2 -> table3 and table3 doesn't "know" what it is linked to in table1. This is used in databases all the time. I need to be able to show that relation without putting an extra column in table 3. Putting them all in the same table will not accomplish this either because the row entry in the combined table still would not have the column populated because it doesn't know what it is linked to in table1. It is a one to many relationship and doing it the other way would make it a many to many relationship. The other problem this would present is if I were to change the relationship of table 1 -> table 2 then I would have to update soooo many things instead of just the one thing.
The work around is mentioned above. Basically use the searching as you would have before and then add your custom search field (the storage will be set to none)... in the template use $cck->get('custom_search_field_id'); to get the field and then extract the value from it and execute your custom query and select the #__cck_core.pk column in your query, and finally use the value to find the item through the query and then use the pk column to find the item... $item = $cck->getItem($result->pk);. Note if the item is null then the result has already been filtered out by other search criteria.
It is a hack and I don't like to hack things but I didn't have another option here. Hopefully they will add this functionality soon.