Wrong syntax for "joinSub" method in mysql driver

[allanmcarvalho]

Laravel Version

11.35.1

PHP Version

8.4|8.3

Database Driver & Version

MySQL 8.0.33 DBngin

Description

The error occurs in Laravel when executing a query that uses the joinSub method. Laravel generates invalid SQL syntax by adding parentheses around the table name in the INNER JOIN clause. Specifically, the SQL generated includes INNER JOIN (receipt_participants) instead of a valid reference to the table or subquery. MySQL does not support parentheses around a table name in this context, leading to the following syntax error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as `issuer` on `issuer`.`id` = `receipts`.`issuer_id`
inner join (receipt_part' at line 5

Steps To Reproduce

1. Set up the database:

• Create the following tables in your MySQL database:
  • receipts (with columns like id, issuer_id, beneficiary_id, deleted_at, and created_at).
  • receipt_participants (with columns like id, name and other participant details).

2. Add the necessary Laravel models for these tables, such as Receipt.
3. Use the following query in your Laravel code:

Receipt::query()
    ->select([
        'receipts.*',
        'issuer.name',
        'beneficiary.name',
    ])
    ->joinSub(
        'receipt_participants',
        'issuer',
        'issuer.id',
        '=',
        'receipts.issuer_id'
    )
    ->joinSub(
        'receipt_participants',
        'beneficiary',
        'beneficiary.id',
        '=',
        'receipts.beneficiary_id'
    )
    ->orderByDesc('created_at')
    ->get();

4. Run the query. Laravel will execute a SQL query similar to the following:

select
  `receipts`.*,
  `issuer`.`name`,
  `beneficiary`.`name`
from
  `receipts`
  inner join (receipt_participants) as `issuer` on `issuer`.`id` = `receipts`.`issuer_id`
  inner join (receipt_participants) as `beneficiary` on `beneficiary`.`id` = `receipts`.`beneficiary_id`
where
  `receipts`.`deleted_at` is null
order by
  `created_at` desc

5. Observe the error in MySQL:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as `issuer` on `issuer`.`id` = `receipts`.`issuer_id`
  inner join (receipt_part' at line 7

6. Working solution (just removing the parentheses)

select
  `receipts`.*,
  `issuer`.`name`,
  `beneficiary`.`name`
from
  `receipts`
  inner join receipt_participants as `issuer` on `issuer`.`id` = `receipts`.`issuer_id`
  inner join receipt_participants as `beneficiary` on `beneficiary`.`id` = `receipts`.`beneficiary_id`
where
  `receipts`.`deleted_at` is null
order by
  `created_at` desc

[allanmcarvalho]

We can just use the join method to make this query work, passing 'table_name as table_alias' in the table parameter. The question remains, should the above code also work?

[staudenmeir]

Hi @allanmcarvalho,

We can just use the join method to make this query work, passing 'table_name as table_alias' in the table parameter.

That's the correct way.

The question remains, should the above code also work?

You are using joinSub() incorrectly, IMO. The method is meant for subquery joins and that's not the case in your example. The first parameter only accepts strings so you can pass a subquery as raw SQL.