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.

file: joomla/templates/seblod_table_copy/index.php

        // EDITED - AUTHOR: BRYAN //

        try {

                $db = JFactory::getDbo();

                $query = $db->getQuery(true);

                $query

                        ->select(array('c.pk AS id', 'COALESCE(d.district_id, county.county_to_district_id, z2c.county_to_district_id) AS district_id','COALESCE(county.county_id,z2c.county_id) AS county_id'))

                        ->from('#__cck_core AS c')

                        ->leftjoin('#__cck_store_form_district d ON (d.id = c.pk)')

                        ->leftjoin('#__cck_store_form_county county ON (county.id=c.pk)')

                        ->leftjoin('#__cck_store_form_zip_code z ON (z.id = c.pk)')

                        ->leftjoin('#__cck_store_form_provider p ON (p.id = c.pk)')

                        ->leftjoin('(SELECT b.title AS zip_code, a.* FROM #__cck_store_form_zip_code a LEFT JOIN #__content ON (a.id=b.id)) AS z2p ON (z2p.zip_code = p.provider_to_zip_code)')

                        ->leftjoin('#__cck_store_form_county z2c ON (z2c.county_id = z.zip_code_to_county_id OR z2c.county_id = z2p.zip_code_to_county_id)');

                $districtField = $cck->get('district_id_search');

                if (!empty($districtField) && !empty($districtField->value)) {

                        $query->having('district_id='.$districtField->value);

                }

                $db->setQuery($query);

                $results = $db->loadObjectList();

        } catch (Exception $e) {

                print_r($e);

        }

        $items          =       array();

        foreach ($results AS $result) {

                $item = $cck->getItem($result->id);

                if (!empty($item)) {

                        $items[] = $item;

                }

        }

        //$items        =       $cck->getItems();

        // END EDITED //

        $positions      =       $cck->getPositions();

Hi

this looks interesting, will look at closer been going to do something with countries and states.

cheers

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.

Hi Bryan,

I would like to understand your need. You explained you would like to be able to filter the zip codes by district. I don't understand.

Can you give us an example?

Regards,
Mehdi.

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 :)

Hi Bryan,

You don't need any code to do that. You need for that to have one database table (if you manage the districts of one country). In this table:

1. you should have a column contains all districts of a country and in the second column,
2. you should have zip code of each district.

After that, we have a specific field to implement this feature. If you have this need, we can propose you this field after payment.

Regards,
Mehdi.

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.

Having just read this topic while looking for something else, I'm wondering why it's been marked "Solved" - it doesn't look very solved to me.

Well I found a work around but you are correct it's technically not solved so I will change it.  Hopefully it will be solved soon.

Could I ask what the workaround involved?

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.