Database table relations and its syntax

we

webbusteruk 05 Jan, 2019

I've read the tutorial, but there are several things I still needed clarification.

1. When any field is put into Model1's "Fields to retrieve", LEFT JOIN stopped working (syntax omitted altogether when viewing debug). No data is retrieved from Model2, regardless if there is anything under Model2's "Field to retrieve". Is this a bug?

Model2's fields to be retrieved need to be included. If there is none of Model2's fields under Model1's "Fields to Retrieve", it simply omits Model2 altogether.

2: How does one do INNER JOIN?

Read_Data function only does LEFT JOIN. To do an INNER JOIN query, you'll need to use PHP code instead.
https://www.chronoengine.com/forums/posts/t106559/how-to-use-custom-database-query-table-view-in-ccv6

3: 'Subquery Join' just comes up with syntax error, no matter what I do. Has anyone figured this out yet?

Still a mystery.

4: We could set the Foreign Key, but what is the Primary Key it's linking to?

Default Primary Key is the first column of the other table (that doesn't contain the Foreign Key). If you need to set the Primary Key, use the Relation conditions field instead (eg. Model1.fieldA:Model2.fieldB).

5: What are Special Fields?

Will update as I find more questions or answers to above.

he

healyhatman 05 Jan, 2019

1 Likes

1) Not a bug, you're probably just doing it wrong.

2) You do an INNER JOIN by making the query yourself in PHP. Pretty sure the multiple tables relation just works on LEFT JOIN. https://docs.joomla.org/Special:MyLanguage/Selecting_data_using_JDatabase

3) Nope, no idea. Been trying to get an answer on that for a year.

4) It's referencing whatever you tell it to. Related to should be model 1. Foreign key will be the field in table 2 you're relating it to, but I like to just use the relation conditions like this

model1.field:model2.field

we

webbusteruk 06 Jan, 2019

I'm not sure where we go wrong in (1).

If we try it on the demo "Article Relations" connection, it does not work either if there's anything in the "Fields to retrieve" box. The query simply does not show LEFT JOIN in the debug. There was no syntax error.

he

healyhatman 06 Jan, 2019

Answer

1 Likes

Are you also retrieving the fields you're trying to join on? Use

model1.field
model2.field

etcetc

we

webbusteruk 06 Jan, 2019

Yes, that's it!

So if there is none of model2's field under "Fields to Retrieve", it simply omits model2 altogether.

This extension is one of the most powerful extension in Joomla, yet sometimes I get so frustrated at the lack of documentation and tutorials. Should there be a community made FAQ or something to help out the developers?

Forums

Database table relations and its syntax