Why am is laravel throwing an SQLSTATE[42000]: Syntax error or access violation: 1064 Error

Question

Am running a query builder on my laravel app using inner join But Laravel keeps throwing errors, i tested the MySql Query i wrote directly in my database using phpMyAdmin it works fine

SELECT 
    u.id, u.first_name, u.last_name, u.username, u.sponsor,u.deleted_at, i.id as investment_id, i.total_amount, i.created_at
FROM
    users AS u 
JOIN investments as i ON 
    i.id= ( SELECT i1.id FROM investments as i1 where u.id=i1.user_id and i1.status ='confirmed' ORDER BY i1.created_at LIMIT 1)
WHERE
    u.sponsor = '901d08da-e6c4-476a-ae7b-9386990b8b9e' AND u.deleted_at is NULL
ORDER BY 
    created_at DESC

but when i write it using query builder it wont work.

$refered_users = DB::table('users')
    ->join('investments', function ($join) {
      $join->on('users.id', '=', 'investments.user_id')
      ->where('investment.status', '=', 'confirmed')
      ->orderBy('investment.created_at','desc')->first();
    })
    ->select('users.id,users.first_name,users.last_name,users.username,users.sponsor,investment.id AS investment_id,investment.total_amount,investment.created_at')
    ->where('users.sponsor','=',Auth::User()->id)
    ->orderBy('investment.created_at','desc')
    ->paginate(10);

i tried a RAW DB::select() and it works but i want to use the query builder so i can paginate the results. this is how my table is arranged:

users id, first_name, last_name, username, sponsor(id of another user), created_at, deleted_at

investments id, total_amount , user_id(id of a user), status, created_at, deleted_at

Am not much Good with SQL Queries so if am writing it all wrong please don't scold just, try to explain a lil bit more so i can understand

this is the error coming out:

Illuminate\Database\QueryException

SQLSTATE[42000]: Syntax error or access violation: 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 'on users.id = investments.user_id and investment.status = ? order by' at line 1 (SQL: select * on users.id = investments.user_id and investment.status = confirmed order by investment.created_at desc limit 1)

output of the dd

select first_name,last_name,username,deleted_at,total_amount,
investment_created_at,user_id from `users` 
inner join (select user_id,total_amount,status,created_at AS investment_created_at from `investments` 
where `status` = ? and `investments`.`deleted_at` is null) as `confirmed_investments` 
on `users`.`id` = `confirmed_investments`.`user_id` 
where `sponsor` = ? order by `investment_created_at` desc ◀`


`array:2 [▼ 0 => "confirmed" 1 => "901d08da-e6c4-476a-ae7b-9386990b8b9e" ]`

am using:

PHP version: 7.3.1

MySql Version: 8.0.18

Replace paginate(10) with dd(). Add the output to your question so we can see the full query it's running. Sample data and migrations would help too. A lot of this could likely be done with Eloquent relationships. — miken32
But first thing I would do is get rid of the orderBy() and first() calls in your subjoin. Doesn't make any sense to have it there. Maybe you want those on $confirmed_investments instead. — miken32
@miken32 heres i updated the question with the output of the dd() — joekenpat
@miken32 i moved the orderBy() and first() to $confirmed_investments now it's empty results...if you can aid me convert the sql query to query builder i'd be happy, i don't want to use raw sql and create a pagination my self because the records from the query might become very large in the future to process with php. — joekenpat
Add migrations and sample data, as well as expected output. Are you trying to find earliest confirmed investment with a given sponsor? Will there be multiple matches or just one? — miken32

train_fox train_fox · Accepted Answer · 2020-03-19T08:27:17

You can get query of your code and compare it to your desired query like this:

$refered_users = DB::table('users')
->join('investments', function ($join) {
  $join->on('users.id', '=', 'investments.user_id')
  ->where('investment.status', '=', 'confirmed')
  ->orderBy('investment.created_at','desc')->first();
})
->select('users.id,users.first_name,users.last_name,users.username,users.sponsor,investment.id AS investment_id,investment.total_amount,investment.created_at')
->where('users.sponsor','=',Auth::User()->id)
->orderBy('investment.created_at','desc')
->toSql();

Your join query is getting data for id by running another query. I think you need to use sub query. If you are using laravel > 6 it is on documentation.

Why am is laravel throwing an SQLSTATE[42000]: Syntax error or access violation: 1064 Error

1 Answers