LeftOuterJoin Complex

btree

Hi,

i try to get a leftjoin with multiple operators working.

-> leftJoin('movedate as movedate2', 'movedate.hive_id', '=', 'movedate2.hive_id'
AND ('movedate.date' '<' 'movedate2.date' 
OR ('movedate.date' = 'movedate2.date'
AND 'movedate.id' < 'movedate2.id'))

Full query should be

Editor::inst( $db, 'hive')
    ->pkey( 'hive_id' )
    ->field(
                Field::inst( 'hive.name' ),
                Field::inst( 'apiary.name' )
            )
    ->leftJoin( 'apiary', 'apiary.apiary_id', '=', 'movedate.apiary_id' )
    ->leftJoin( 'movedate', 'hive.hive_id', '=', 'movedate.hive_id' )
    //left outer join 
    -> leftJoin('movedate as movedate2', 'movedate.hive_id', '=', 'movedate2.hive_id' AND ('movedate.date' '<' 'movedate2.date' OR ('movedate.date' = 'movedate2.date' AND 'movedate.id' < 'movedate2.id'))
    ->where( 'hive.modus', '1' )
    ->where( 'apiary.user_id', $user_id )
    ->where ('movedate2.hive_id', NULL)
    ->process( $_POST )
    ->json();

Is there anything like the WHERE closure function ?

$editor->where( function ( $q ) {
    $q
        ->where( 'age', '18', '>' )
        ->or_where( function ( $r ) {
            $r->where( 'name', 'Allan' );
            $r->where( 'location', 'Edinburgh' );
        } );
} );

Cheers
Hannes

btree

Update: Looks like the complex WHERE function works with leftJoin, but now the Code ignore my ->where conditions from the main table?

---

Edit: I'm stupid - the -where function doesnt know it belongs to the leftJoin .... is there a way to combine the where function into the left join?

---

$editor-> leftJoin('movedate as movedate2', 'movedate.hive_id', '=', 'movedate2.hive_id')
        ->where( function ( $q ) {
            $q
            ->where ('movedate.date', 'movedate2.date', '<')
            ->or_where( function ( $r ) {
                $r
            ->where( 'movedate.date', 'movedate2.date' )
                ->where( 'movedate.id', 'movedate2.id', '<' );
                });
        });

allan

Hi Hannes,

Thanks for your posts. I'm sorry to say that at the moment that the leftJoin method does not currently support complex conditions such as what you are looking for. However, this is something I would like to add to Editor. I'll take a look at it today and see how much work is involved, and hopefully make some progress in that direction.

Regards,
Allan

btree

Thank you for your quick answer. I'll use my old SQL Code without Server-Side processing. I hope you will be able to include this function because Server-Side processing was the main reason to buy editor for me.

Cheers
Hannes

allan

Due to the way the current implementation works you can do something like this:

->leftJoin( 'movedate as movedate2', 'complexExpression', '', '' )

where complexExpression is your SQL condition - for example:

movedate.hive_id = movedate2.hive_id AND (
  movedate.date < movedate2.date OR (
    movedate.date = movedate2.date AND
    movedate.id < movedate2.id
  )
)

Its a bit of a hack, but it should work nicely. I will have a think about how best to make this "official".

Allan

btree

Great hack! Moved the complex to the last position, looks nicer for me. ;)

->leftJoin( 'movedate as movedate2', 
        'movedate.hive_id', '=',
                'movedate2.hive_id AND 
                (movedate.date < movedate2.date OR 
                (movedate.date = movedate2.date AND 
                movedate.id < movedate2.id))'
               )

allan

Quick update on this for anyone interested in this topic - I've been looking into formalising this approach in Editor and was going to allow a closure function to be passed in which could add an arbitrarily complex condition to be used.

However, Editor provides the ability to update the joined table, so it currently needs to "understand" the join condition that is being used so it can determine what fields need to be updated. This ability to edit the joined tables might or might not be important for you, but it is a feature of Editor, so any integration needs to take this into account.

It would be possible to detect if a closure function is given and have it reject any updates to the joined tables, and I might take this approach in future, but I think this is something that is worth of significant reflection before being implemented in case I change things in future creating further difficulties.

We have the "hack" discussed above in the short term.

Regards,
Allan

stol

This worked well for me. One note - when I had the 'complexExpression' on multiple lines as displayed above, it truncated after the first AND and got a sql error. When I removed the line breaks, it worked very well.