Yii2 query error

Question

I have this query in yii2 :

SELECT COUNT(*) 
FROM `agencias` 
    LEFT JOIN `responsables` `rsp` 
        ON `agencias`.`responsable` = `rsp`.`responsable_id` 
    LEFT JOIN `receptores` `rec` 
        ON `agencias`.`receptor` = `rec`.`receptor_id` 
WHERE (`nombre` LIKE '%pru%') 
    AND (nombres LIKE "%%" or apellidos LIKE "%%")

But i get the message 'Column 'nombre' in where clause is ambiguous' i know how to fix this error setting an alias for the where conditions but dont know how to this is in yii2 activequery.

So the question is how i can set aliases for the where conditions to get a query like this :

SELECT COUNT(*) 
FROM `agencias` 
    LEFT JOIN `responsables` `rsp` 
        ON `agencias`.`responsable` = `rsp`.`responsable_id` 
    LEFT JOIN `receptores` `rec` 
        ON `agencias`.`receptor` = `rec`.`receptor_id` 
WHERE (`nombre` LIKE '%pru%') as alias1 
    AND (nombres LIKE "%%" or apellidos LIKE "%%") alias 2

Did you try adding agencias. before nombre? (considering this column is indeed in the agencias table) — Romain
– Romain, Commented Aug 1, 2015 at 20:00
yes, and it doesn't change the result because it still bring the name(nombre) of agencias and the name(nombre) of receptor, so there is where the problem happends. — Luis Contreras
– Luis Contreras, Commented Aug 1, 2015 at 20:04

ttzn · Accepted Answer · 2015-08-01 20:02:09Z

2

You need to prefix the nombre field in the WHERE clause with the right table alias. Suppose you're filtering based on the nombre field in responsables, then your query should be :

SELECT COUNT(*) 
FROM `agencias` 
    LEFT JOIN `responsables` `rsp` 
        ON `agencias`.`responsable` = `rsp`.`responsable_id` 
    LEFT JOIN `receptores` `rec` 
        ON `agencias`.`receptor` = `rec`.`receptor_id` 
WHERE (`rsp`.`nombre` LIKE '%pru%') 
    AND (`nombres` LIKE "%%" or `apellidos` LIKE "%%")

answered Aug 1, 2015 at 20:02

ttzn

2,62324 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Yii2 query error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related