Question 16804 in Laravel Question posted in
The official Laravel Documentation can be found here.

MySql join statements order (using Eloquent ORM) – Laravel

I use Laravel Eloquent ORM in my project.
I need to duplicate a base query in order to create different queries, and as a by-product I have a problem with the order of some JOIN statements.

In order to simplify the problem, this query shows the problem I reach:

SELECT *
FROM A
JOIN C ON C.x = B.x
JOIN B ON B.y = A.y;

Executing this query, I get the following error:

ERROR 1054 (42S22): Unknown column 'B.x' in 'on clause'

I assume the reason is that when the SQL parser reaches the first join, the table B is not included yet. However, the code becomes very clumsy if I switch these two lines (see explanation at the end).

Is there any way to execute this query in this format?
Alternatively, is there any way to inject the second join statement before the first one using eloquent query builder?

Why can’t I switch JOIN order?
I have a base query, which is common to different queries I need to execute:

SELECT * FROM A
JOIN C ON C.x = B.x

The key of the second JOIN (B table) depends on the final query I want to execute. For example, I might have:

   :
JOIN B ON B.y = A.y1

And in another query:

   :
JOIN B ON B.y = A.y2

In eloquent the (very simplified) code looks like:

$baseQuery = DB::table('A')->join('C', 'C.x', '=', 'A.x');

$query1 = (clone $baseQuery)->join('B', 'B.y', '=', 'A.y1');
$query2 = (clone $baseQuery)->join('B', 'B.y', '=', 'A.y2');
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I assume this is a very specific problem, still I will post here my solution hopק this helps someone someday.
    Again, the query I build is much bigger that in the example, so I was looking for a solution to avoid duplicating code.

    The solution I found is creating a function that adds the common part of the query:

    public function myFunc()
{
    $baseQuery = DB::table('A');

    $query1 = (clone $baseQuery)->join('B', 'B.y', '=', 'A.y1');
    $query2 = (clone $baseQuery)->join('B', 'B.y', '=', 'A.y2');

    $this->addCommonQueryPostfix($query1);
    $this->addCommonQueryPostfix($query2);
}

private function addCommonQueryPostfix(IlluminateDatabaseQueryBuilder &$query)
{
    $query->join('C', 'C.x', '=', 'B.x');
}

    Which will create the following queries, as expected:

    SELECT * FROM A INNER JOIN B ON B.y = A.y1 INNER JOIN C ON C.x = B.x;
SELECT * FROM A INNER JOIN B ON B.y = A.y2 INNER JOIN C ON C.x = B.x;

  2. 0

    SELECT *
    FROM A
    JOIN B ON B.y = A.y
    JOIN C ON C.x = B.x;

    if you will run above query in this way it will return the same result.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search