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

Multiple joins in OO notation fail

I'm having difficulties querying the database using the OO notation to build queries if I use more than one join (INNER in my case). Here is the code:

        $query = $this->query()
                      ->columns('Products.id, product_name, product_key, sku')
                      ->join('Skus', 'Products.id = Skus.product_id', 's')
                      ->join('Clients', 'Products.client_id = Clients.id')
                      ->where('client_hash = :client_id:')
                      ->where('product_key = :product_key:')
                      ->bind(array('client_id' => $client_id, 'product_key' => $product_key))
                ;

The table I'm querying on belongs to "Client" and has many "Skus" — all those relationships are defined in the models in the initialize() method.

When I try to execute() the resulting query, I get the following exception:

PhalconException: Scanning error before '] JOIN [] JOIN [...' when parsing: SELECT Products.id, product_name, product_key, sku FROM [Products] JOIN [Skus] AS [s] ON Products.id = Skus.product_id JOIN [] JOIN [] JOIN [] JOIN [] WHERE product_key = :product_key: (184)

The join works perfectly when I use only one join — either Skus or Clients.

Is this a bug or is there something I'm missing?

Thanks,

Torsten



1.3k

I'm having this same issue. Did you ever find a solution?



3.2k
edited Mar '14

same issue here. I'm guessing the problem is the multiple JOIN [] JOIN [] JOIN [] JOIN [] added in the query. @phalcon

EDIT: 1.3.0 has the same issue :(

EDIT2: unit tests don't seem to test multiple joins: https://github.com/phalcon/cphalcon/blob/master/unit-tests/ModelsCriteriaTest.php



1.3k

I came to the same conclusion. I was never ever to solve the problem. @maxgalbu



3.2k
edited Mar '14

Well, i just found out that using Phalcon\Mvc\Model\Query\Builder instead of model::query() things starts to work:

old code example (taken from first post):

$query = $this->query()
  ->columns('Products.id, product_name, product_key, sku')
  ->join('Skus', 'Products.id = Skus.product_id', 's')
  ->join('Clients', 'Products.client_id = Clients.id')
  ->where('client_hash = :client_id:')
  ->andWhere('product_key = :product_key:')
  ->bind(array('client_id' => $client_id, 'product_key' => $product_key));
$products = $query->execute();

new code example:

$query = new Phalcon\Mvc\Model\Query\Builder();
$query->addFrom("Products")
  ->columns('Products.id, product_name, product_key, sku')
  ->join('Skus', 'Products.id = Skus.product_id', 's')
  ->join('Clients', 'Products.client_id = Clients.id')
  ->where('client_hash = :client_id:')
  ->andWhere('product_key = :product_key:')
  ->bind(array('client_id' => $client_id, 'product_key' => $product_key));
$products = $query->getQuery()->execute();

tried to look at the code but couldn't find what is the difference between Phalcon\Mvc\Model\Criteria and Phalcon\Mvc\Model\Query\Builder. Seems like they could share some code?



3.2k

I'm trying to fix this bug in this pull request:

https://github.com/phalcon/cphalcon/pull/2220



3.7k

Same problem here! Any fix coming soon?