We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Unable to use multiple left joins with createBuilder

Maybe I am doing something wrong here, but I am unable to join on multiple tables when using createBuilder(). Here is an example query.

$test = $this->modelsManager->createBuilder()
        ->from('TABLE1')
        ->leftJoin('TABLE2', 'TABLE1.id = TABLE2.table_one_id')
        ->leftJoin'TABLE3', 'TABLE3.id = TABLE2.table_three_id')
        ->where('TABLE1.id = :id:', array('id' => $id))
        ->groupBy(array('TABLE1.id'))
        ->getQuery()
        ->execute();

There error indicates that the SQL query is probably not being generated correctly by the framework, but I could very well be doing something wrong. It appears that no space is being added before the additional LEFT join.

Unknown column 'TABLE2.table_one_idLEFT' in 'on clause''

Any insight would be greatly appreciated.

3


98.9k

I think there is a bug there, let me check it



2.1k

Thank you, I am hoping it is some sort of easy fix as I'd like to test the framework's ORM some further. Let me know what you find out and thanks again.

As a side note, I did not see the same issue when using multiple inner joins.



98.9k
Accepted
answer

@johncharrell, this is fixed in the 1.2.0 branch, thank you



2.1k

Excellent! Thank you very much.



1.7k
edited Oct '14

Another problem here.. if i try to join the same table several times. for example:

<?php
$test = $this->modelsManager->createBuilder()
        ->from('TABLE1')
        ->leftJoin('TABLE2', 'TABLE1.id = TABLE2.table_one_id')
        ->leftJoin'TABLE2', 'TABLE2.id = TABLE1.table_three_id')

the resulting sql statement will contain only the last join, but phql contains both of them



98.9k

@serafimovich Can you try again compiling from the 1.2.0 branch, I just added a fix for that, also you will need some aliases for the models:

$test = $this->modelsManager->createBuilder()
        ->from('Model1')
        ->leftJoin('Model2', 'Model1.id = a.table_one_id', 'a')
        ->leftJoin('Model2', 'b.id = Model11.table_three_id', 'b')


1.7k

thanks, but i'm not able to try for now.. tried to build 1.2.0, but it fails at compile time on my machine (



1.7k

checked with 1.2.0, looks like fixed, many thanks