SQLSTATE[42S22]: Column not found: 1054 Unknown column '4' in 'where clause why' i cannot find the mistake

Question

i have a select wuery in my php/laravel code the problem is when i run it it gives this error

 SQLSTATE[42S22]: Column not found: 1054 Unknown column '4' in 'where clause'
where (`element_value`.`p_id` = `4`))

this is my query

DB::table('value_tbl')
    ->select(DB::raw("CONCAT(values.value_name,'/',element_value.qan) AS full_value"))
    ->leftJoin('element_value','element_value.element_id','value.element_id')
    ->where (['element_value.p_id'=>$a->p_id])->get()->pluck('full_value')->toArray())

where (`element_value`.`p_id` = `4`)) into where (`element_value.p_id` = `4`)) You have concetanated the two strings and now it reads `element_valuep_id` instead of `element_value.p_id` — Trukken, Jun 28 '21 at 12:40
`where (`element_value`.`p_id` = `4`))` this line is in the error returned by the page..the code i have written is above — Malo, Jun 28 '21 at 12:41
The 4 is most likely coming from `$a->p_id`. However, it should be passing it as a value instead of a column name, so that's very curious. — aynber, Jun 28 '21 at 12:46
If you are to use an operator the where function takes 3 args: First the name of the column. Second is an operator, and third is value to compare against. — brizzy_p, Jun 28 '21 at 12:51
@brizzy_p is bang on the money. Your problem is the key-value array passed to where. That's not how the method wants values to be passed. — JustCarty, Jun 28 '21 at 13:09

score 5 · Accepted Answer · edited Jun 28 '21 at 13:05

If you are to use an operator the where function takes three arguments: first the name of the column, second is an operator, and third is value to compare against.

Change your condition to appear as below

->where('element_value.p_id', '=', $a->p_id)
// or, because you are making an exact match, do this
->where('element_value.p_id', $a->p_id)

The code you have at the moment is getting confused because you are telling Laravel you are making a multiple where condition, through the use of an array as the where first argument.
Laravel then takes the value of that array and tries to convert it to column, operator, and value - the way it does for the above snippet.

If you really want to use an array, you'd need to do the following:

->where([
    ['element_value.p_id', '=', $a->p_id]
])
// or, because you are making an exact match, do this
->where([
    ['element_value.p_id', $a->p_id]
])

Notice how we passed two sets of arrays?
That's because Laravel either wants each argument separate, or an array-of-arrays containing the correct signature.

For a more detailed answer; the signature looks like this

public function where($column, $operator = null, $value = null, $boolean = 'and')

If $column is an array, it is assumed that an array-of-arrays is passed into the method, and each sub array, will effectively be spread across the above signature.

As you were passing a single array to the method, Laravel was taking the value of 4 and then using that as the $column.
This is because you used a key-value for that array, so the column name that you had used was actually being ignored.

The other interesting thing about this method, is that it has the following snippet:

// If the given operator is not found in the list of valid operators we will
// assume that the developer is just short-cutting the '=' operators and
// we will set the operators to '=' and set the values appropriately.
if ($this->invalidOperator($operator)) {
    [$value, $operator] = [$operator, '='];
}

This allows us to not pass in an operator, and Laravel will just assume that we want an = to be used instead.
That's how I was able to omit it from my above examples.

If you are to use an operator the where function takes 3 args: First the name of the column. Second is an operator, and third is value to compare against. — brizzy_p, Jun 28 '21 at 12:50
You can also completely negate the `=`. The point is, it shouldn't be a key-value pair in the where clause. — JustCarty, Jun 28 '21 at 12:53

SQLSTATE[42S22]: Column not found: 1054 Unknown column '4' in 'where clause why' i cannot find the mistake

1 Answers1