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

Laravel Sail – SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

Anyone knows why this query is returning error? I’m using Laravel Sail with Mysql version: 8.0.32, it should work:

Property::select(DB::raw("
            properties.id,
            properties.company_id,
            properties.title,
            properties.slug,
            properties.neighborhood,
            properties.city,
            properties.address,
            properties.number,
            properties.property_purpose,
            properties.property_type,
            properties.total_area,
            properties.usefull_area,
            properties.bedrooms,
            properties.bathrooms,
            properties.garage,
            properties.sell,
            properties.rent,
            properties.administration_fee,
            properties.yearly_tax,
            property_highliteds.hightlight,
            companies.property_show_number,
            ROW_NUMBER() OVER (PARTITION BY properties.company_id ORDER BY property_highliteds.hightlight DESC, properties.created_at DESC) as row_number
        "))
            ->leftJoin('property_highliteds', 'properties.id', '=', 'property_highliteds.property_id') // Relacionamento com os destaques
            ->leftJoin('companies', 'properties.company_id', '=', 'companies.id')   // Relacionamento com as empresas
            ->orderBy('row_number')  // Ordena pela intercalação dos imóveis
            ->with([
                'images_thumb:id,property_id,url',  // Carrega apenas a URL das imagens
                'company:id,property_show_number',  // Carrega o campo necessário de Company
                'highlight:id,property_id,hightlight' // Carrega o destaque via BelongsTo
            ])
            ->paginate($perPage);

And the error is:

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 'DESC) as row_number from `properties` left join `companies` on `propert' at line 23

Tryed almost anything with ChatGpt and google search. My mysql is correct.

Tags:

2

Answers


  1. 0

    My mysql is correct.

    Just repeating what the others have said, you need to post the actual SQL query this code actually generates (->toSQL()) otherwise you’ll never know that for sure (and we probably won’t be able to help, because at first glance this code does appear to be fine).

    Login or Signup to reply.
  2. 0

    When using the ROW_NUMBER() function in SQL, you cannot directly use the alias (like row_number) in the ORDER BY clause of your main query. Instead, you must order by the actual columns that were used to create that row number.

    Use :

    orderBy(DB::raw("property_highliteds.hightlight DESC, properties.created_at DESC"))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search