How to fix Laravel query builder where clause integer variable translated to string

Question

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))"

The error states the column name is the issue, not the integer - string conversion. Please do recheck the column names in your join clause. — Hari Harker
– Hari Harker, Commented Jun 6, 2019 at 9:00
which type has categories_translation.languages_id column? — Roman Meyer
– Roman Meyer, Commented Jun 6, 2019 at 9:00

Amarnasan · Accepted Answer · 2019-06-06 09:00:35Z

1

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]
])

answered Jun 6, 2019 at 9:00

Amarnasan

15.7k7 gold badges35 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Julius Fasema · Accepted Answer · 2019-06-06 09:11:17Z

there is another thing i just discover with your code. when joining table, you are suppose to be joining 'categories_translation.languages_id' with another table id field. in your case, it is not so. you are not joining 'categories_translation.languages_id' with any table field. so ideally, what you are going to do is this

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');    
                    })
                   ->where([
                        ['parent_id' ,'=', '0'],
                        ['categories.id', '=', $id]
                        ['categories_translation.languages_id', '=', $language]
                    ])
                    ->get();
return $categories;

}

hope this helps

Collectives™ on Stack Overflow

How to fix Laravel query builder where clause integer variable translated to string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related