Nested Joins in query

kicks66

I have a datatable editor instance, using the PHP backend to do most of the heavy lifting.

The main table is a list of users and their posts.

I have another table that converts those users ids into "externalIds".

I then want to join that table with another table, which contains those users "external posts"

And ideally I want to join those with another table which contains those "external posts tags"

Is it possible to do nested joins like this?

i.e. the query i want to imitate looks like:
SELECT
user_id,
external_id,
COUNT(tags)
FROM users
LEFT JOIN externalUsers
as users.id = externalUsers.internalId
LEFT JOIN externalPosts
as externalPosts.user_id = externalUsers.id
LEFT JOIN externalTags
as externalTags.post_id = externalPosts.id

Is it possible to do something like this?

Editor::inst($db, 'users')
-> fields (
etc...
)
->join(
Mjoin::inst('externalUsers')
->link (etc...)
// and here do another nested join?

rf1234

The SQL:

SELECT a.user_id, a.external_id, COUNT(a.tags) AS tag_count
FROM users a
LEFT JOIN externalUsers b   ON a.id = b.internalId
LEFT JOIN externalPosts c   ON c.user_id = b.id
LEFT JOIN externalTags d    ON d.post_id = c.id

looks a little strange to me. Why are you left joining if all you want to show is fields from the users table? How should this work with an aggregate function but witout GROUP BY? Why would you do an Mjoin in Editor if all you want are LEFT JOINS?

But maybe I am missing something.

And this is what it would look like. Will probably produce poor results though ... Editor can't do GROUP BY by the way, but I resolved it in a different fashion using a getFormatter.

Editor::inst( $db, 'users' )
    ->field(
        Field::inst( 'users.user_id' ),
        Field::inst( 'users.external_id' ),
        Field::inst( 'users.user_id AS tag_count' )->set( false )
            ->getFormatter( function($val, $data, $opts) use ($db){
                $result = $db->raw()
                    ->bind( ':fk', $val )  
                    ->exec( 'SELECT COUNT(*) AS tag_count 
                               FROM users  
                              WHERE user_id = :fk' );
                $row = $result->fetch(PDO::FETCH_ASSOC);
                return $row["tag_count"];
            })                 
    )
    ->leftJoin( 'externalUsers', 'users.id', '=', 'externalUsers.internalId' )
    ->leftJoin( 'externalPosts', 'externalPosts.user_id', '=', 'externalUsers.id' )
    ->leftJoin( 'externalTags', 'externalTags.post_id ', '=', 'externalPosts.id' )
    ->process($_POST)
    ->json();

rf1234

should probably be COUNT(tags) but that would mean that those tags should be in a different table than the users table which they aren't according to your SQL. If each user has just one, why count them? But anyway ...