Dynamic Gets - Joining Tables for a front end view? Help!?

[stargazerbooks]

So, I have a MAPS table with map data, and state name and abbreviations for all the US states.

I have a resources table, that contains urls of the specific state resource citations.

The Resource Table is linked to the MAPS table, through a column called state_id in the resource table, where state_id from resources equals the ID from the MAPS table.

There may be multiple resource entries for each state in the database.

When I open the front end, single MAP view in the Joomla 5 front end, it posts a state ID like this: [domain_root]/index.php/component/cuisine/state?id=31 Where 31 represents the state of Georgia.

I need to pass the 31 to the resources table, and retrieve the (potential) multiple resource entries for Geaorgia to the single MAP site view, and return "no data" if there are no resource entries in the database for the given state. (I'm just using Georgia as an example.)

I got this working in the IDE version of my custom component by entering a query in the model that said "WHERE state_id = $this->item->id" . . . But in JCB that doesn't seem to be working. I am only getting out ALL of the entries that are in the resources table.

I have created a Dynamic Get for the MAPS table and this drives the MAP (singular) view in the front end. This works fine and shows the map for the individual state when I pass the MAPS table ID to it.

However, when I create a second Dynamic get for the resources table, and JOIN it to the MAPS table, I have tried several things to pull out just the entries "WHERE state_id = $this->item->id"

The resources table is #__[my_component_name]_state_resources table.

I create a dynamic get in JCB. Here's some things I've tried so far, but I've been at this for a few days.

Get Type -> getCustoms (Should return multiple values in a list)
I enter Main Source -> Back End View then select the Resources Admin View (which grabs all from the above table.)

a.id AS id
a.asset_id AS asset_id
a.state_id AS state_id
a.guideline_type AS guideline_type
a.description AS description
a.link_text AS link_text
a.link_url AS link_url

I am trying to restrict the returned RESOURCES data to only the state currently shown int he front end MAP view.

On the TWEAKS tab of the Dynamic Get, I can add WHERE a.state_id EQUALS 31 <- this works, but will return the RESOURCES data for the state of Georgia no matter which state I select in the front end. (The ID for the state fo Georgia in the maps table is 31 obviously)

I have also tried the following:
WHERE a.state_id EQUALS $this->item->id <- this returns no results, even though item->id ; ?> shows the correct and different state_id in the front end view I am trying to edit.

WHERE a.state_id EQUALS a.id <- obviously doesn't work because a.id is the id of the element of the RESOURCES table and not the id of the state from the MAPS table.

WHERE a.state_id EQUALS b.id <- I think this returned all of the values in the table and not just for the selected ID.

I have also tried creating a Main Source as the Maps table, and then JOIN it to the RESOURCES table:

then added WHERE a.id EQUALS b.state_id <-returned no results

and tried to use various filters and WHERE statements to get them to just output the resources for the selected state . . . but no luck.

I just don't seem to understand the paradigm of how to pass the current MAP id to the resources table query in JCB . . . any direction you could point me in would be more helpful than what I've tried so far. :)

I have also watched the youtube turtorial on dynamic gest from the developer . . . but I don't see where it discusses joining two tables in a site view. I also searched here for similar discussions . . . but my searches returned no results.

Please help . . . JCB is obviously very powerful, and I am looking forward to getting beyond this hump so that I can add more views to my component a lot easier than I was doing it witht he IDE. I appreciate your time and assistance.

[stargazerbooks]

After 3 days of struggle to find documentation on this, cobbling together what does exist, and trying to infer what to do from decade old guidance . . . I think I finally found a solution. I hope this helps others who may be new to JCB.

For Joomla 5, you create a new Dyanmic Get:

Get type: Either getCustom or getCustoms (depending on whether you want a single entity, or multipl etities returned.)

Main TAB:
Main Source: Back End View
View Table: Select the Admin View that pulls data from the table you want to pull data from. (No need to join tables to compare IDs in my case.)
Selection: Select only the columns you need in your view.

Tweak TAB:
Create new filter:
Filter Type: FunctionVar
State Key: Factory::getApplication()->input->getInt('id') <---This is the ID grabbed form the URL of the front end Site View you're editing here.
Comparison Operators: EQUALS (=)
Table key: a.state_id <- this is the column that corelates to the MAIN TABLE ID you are adding data to.

Also on the TWEAK TAB You can add WHERE statements to further filter your returned result(s).

Join TAB:
As stated above no need to join tables in my case. So I left this blank.

This created a MySQL query that only pulled data from the RESOURCES TABLE if the state_id column from the RESOURCES TABLE equals the ID form the STATE_MAP table and front end site view.

I also assume there must be a Joomla! Power entity that would also help you port this to other versions of Joomla, but I'm happy with this Joomla 5 version for now.

Ughh . . . what a frustrating learning curve for this newbie . . . but I am SO glad that I got this figured out and that I can actually move forward again on my project! Yay!