We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. ORM

Phalcon\Mvc\Model\Query\Builder does not work correctly

My script create sql query with joins, for this it using Phalcon\Mvc\Model\Query\Builder, for each table column script add table name alias, if I want add some filtering in where, script add table alias to this column too automatically, but if in one query two table have column with the same name, like id, name and I want to filtering by this column I have error like 'Column 'id' in where clause is ambiguous', but if I log sql query before execute it query looks like "Select table.id, table.name From table join left tabe2 where table.id = '1' and table.name = 'test';" but in mysql query log this query looks different "Select table.id, table.name From table join left tabe2 where id = '1' and name = 'test';" and of course I have error because column with name id or name have table and table2.

Why Phalcon\Mvc\Model\Query\Builder or maybe after execute method query changing? and how I can switch off this?



8.1k

Paste your query, please.



10.3k
edited Oct '14 
$queryBuilder->getPhql();
SELECT event.id AS id, event.name AS name, Member.name AS member, Member.id AS member_id, Location.city AS location, Location.id AS location_id 
FROM [Event\Model\Event] AS [event] 
LEFT JOIN [\Event\Model\Member] AS [Member] ON event.member_id = Member.id 
LEFT JOIN [\Event\Model\Location] AS [Location] ON event.location_id = Location.id 
WHERE ((event.id = '1') OR (event.name LIKE 'test%')) ORDER BY event.id desc

After execute in query log with error

SELECT `event`.`id` AS `id`, `event`.`name` AS `name`, `Member`.`name` AS `member`, `Member`.`id` AS `member_id`, `Location`.`city` AS `location`, `Location`.`id` AS `location_id` 
FROM `event` AS `event` LEFT JOIN `member` AS `Member` ON `event`.`member_id` = `Member`.`id`  
LEFT JOIN `campaign` AS `Campaign` ON `event`.`campaign_id` = `Campaign`.`id`  
LEFT JOIN `location` AS `Location` ON `event`.`location_id` = `Location`.`id`  
WHERE ((`id` = '1') OR (`name` LIKE 'test%')) ORDER BY `id`


8.1k

Show your php code of builder this query.



10.3k
edited Oct '14 
$dataSource = new \Phalcon\Mvc\Model\Query\Builder();
$dataSource->addFrom('\Event\Model\Event', 'event');
$dataSource->columns(['id' => 'event.id', 'name' => 'event.name', 'member' => 'Member.name']);
$dataSource->leftJoin('\Event\Model\Member', 'Member.id = event.member_id', 'Member');
$dataSource->where("event.id = '1'");
$dataSource->andWhere("event.name = 'test'");
$sql = $dataSource->getPhql();
echo $sql; // print "SELECT event.id AS id, event.name AS name, Member.name AS member, Memebr.id AS mid FROM [\Event\Model\Event] AS [event] LEFT JOIN [\Event\Model\Member] AS [Member] ON Member.id = event.member_id WHERE (event.id = '1') AND (event.name = 'test')"
$dataSource->getQuery()->execute(); // [ERROR] SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous
// In sql log "SELECT `event`.`id` AS `id`, `event`.`name` AS `name`, `Member`.`name` AS `member`, `Member`.`id` AS `mid` FROM `event` AS `event` LEFT JOIN `member` AS `Member` ON `Member`.`id` = `event`.`member_id`  WHERE (`id` = '1') AND (`name` = 'test')"


8.1k
edited Oct '14

Try change

$dataSource->columns(['id' => 'event.id', 'name' => 'event.name', 'member' => 'Member.name']);

to

$dataSource->columns([ 'event.id',  'event.name', 'Member.name']);

and change by replacing addFrom and columns. Builder work like normal SQL query. columns --> SELECT addFrom -- FROM ... and so on You can use Xdebug for detailed trace. It work nice.



10.3k

but I want add aliases to each column, why script each time replace full column path to alias in where section if don't want it? I think that it is not correct.



8.1k

Then create aliases that do not coincide with the actual field names. Error code you imeeno says about it. I use Xdebug for tuning SQL queries in Phalcon. It show low level query. It's good assists.