Ad

How To Fix Laravel Query Builder Where Clause Integer Variable Translated To String

- 1 answer

I have a function to get a pass a language number to get language categories record for API purpose. I use a database query statement to select categories table and join the category language table to get category id, parent_id and name (specified language). When execute return error and select the underlying SQL converted the language value to string (e.g. languages_id = 1). I google a lot and no ideas what's wrong. Can anyone advise how to resolve. Thanks a lot.

I tried to copy the underlying SQL to MySQL Workbench and remove the languages_id = 1 -->languages_id = 1 can working properly. I guess the 1 caused error.

Code Sample:

private function getCategories($language) {
    $categories = DB::table('categories')
        ->select(DB::raw('categories.id, categories.parent_id, categories_translation.name'))
        ->join('categories_translation', function($join) use ($language) {
            $join->on('categories_translation.categories_id', '=', 'categories.id');
            $join->on('categories_translation.languages_id', '=', $language);
        })
        ->where([
            ['parent_id' ,'=', '0'],
            ['categories.id', '=', $id]
        ])
        ->get();
    return $categories;
}

Error return the converted SQL:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column '1' in 'on clause' (SQL: select categories.id, categories.parent_id, categories_translation.name from categories inner join categories_translation on categories_translation.categories_id = categories.id and categories_translation.languages_id = 1 where (parent_id = 0 and categories.id = 1))"

Ad

Answer

You are trying to join using a comparison to an scalar value, instead of a column. I think you actually want to put that comparison as a "where" condition, rather than a "join on"

->where([
     ['parent_id' ,'=', '0'],
     ['categories.id', '=', $id],
     ['categories_translation.languages_id', '=', $language]
])
Ad
source: stackoverflow.com
Ad