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

Laravel Eloquent – SQLSTATE[42000]: Syntax error or access violation:

I’m trying to get all of a users completed habits, although I’m getting a syntax issue when using eloquent?

What’s going on here… syntax seems fine to me.

Query:

$completedHabits = Habit::query()
            ->where('user_id', auth()->user()->id)
            ->join('habit_goals', 'habits.id', '=', 'habit_goals.habit_id')
            ->whereHas('progressLogs', function($q) {
                $q
                    ->whereRaw('created_at >= SUBTIME(created_at, INTERVAL habit_goals.interval SECOND)')
                    ->select(DB::raw('SUM(value) as valueCommited'))
                    ->havingRaw('valueCommited > habit_goals.value');
            })
            ->orderBy('habit_goals.created_at', 'DESC')
            ->get();

Full error:
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 ‘) having valueCommited > habit_goals.value) order by habit_goals.created_at ‘ at line 1

Tags:

2

Answers


  1. 0

    The mistake you’re encountering arises from a common error in employing the havingRaw clause within Laravel Eloquent. The problem stems from your attempt to utilize the alias valueCommited in the havingRaw clause, but this alias isn’t recognized at that juncture within the query.

    To rectify this issue, you can employ a subquery to compute the valueCommited and then reference it within the having clause. Here’s how you can adjust your query to address this problem:

    $completedHabits = Habit::query()
    ->where('user_id', auth()->user()->id)
    ->join('habit_goals', 'habits.id', '=', 'habit_goals.habit_id')
    ->select('habits.*') // Choose the desired columns from the habits table
    ->selectSub(function ($query) {
        $query->from('progress_logs')
            ->selectRaw('SUM(value)')
            ->whereRaw('progress_logs.habit_id = habits.id')
            ->whereRaw('progress_logs.created_at >= SUBTIME(progress_logs.created_at, INTERVAL habit_goals.interval SECOND)')
            ->groupBy('progress_logs.habit_id')
            ->havingRaw('SUM(progress_logs.value) > habit_goals.value');
    }, 'valueCommited')
    ->orderBy('habit_goals.created_at', 'DESC')
    ->get();

    In this revised query, I’m utilizing the selectSub method to construct a subquery that calculates the valueCommited. The subquery computes the sum of values for each habit, comparing it to the habit_goals.value and ensuring that it exceeds this value. This subquery is subsequently given the alias valueCommited, which can then be used in the orderBy clause of the primary query.

    Login or Signup to reply.
  2. 0

    The error you’re encountering is due to a MySQL syntax issue in your query. Specifically, the error is happening because you’re using the havingRaw method with an alias (valueCommited) that is calculated within a subquery.

    In MySQL, you cannot reference aliases defined in the SELECT clause in the HAVING clause directly. To work around this, you need to wrap the entire query in a subquery and then reference the alias in the outer query’s HAVING clause.

    Here’s the modified query:

    $completedHabits = Habit::query()
    ->where('user_id', auth()->user()->id)
    ->join('habit_goals', 'habits.id', '=', 'habit_goals.habit_id')
    ->whereHas('progressLogs', function($q) {
        $q
            ->whereRaw('created_at >= SUBTIME(created_at, INTERVAL habit_goals.interval SECOND)')
            ->selectRaw('SUM(value) as valueCommited')
            ->groupBy('progressLogs.habit_id')
            ->havingRaw('SUM(value) > habit_goals.value');
    })
    ->orderBy('habit_goals.created_at', 'DESC')
    ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search