xPDO fails on if statement because of columnification

6 Posts

eelkevdbos Reply #1, 15 years, 2 months ago

IF(cola = colb, 1, 0) gets converted to IF(`cola` = `colb`, `1`, 0)

This leads to a ’unknown field 1 in columnlist’. I solved this problem replacing the 1 with 1.0, but this no sustainable solution in my opinion since xPDO sould not convert INT to columnnames.

Env: modx revo 2.1.1-pl

863 Posts

Lucas Reply #2, 15 years, 2 months ago

Want to post your whole query and a brief description of what you want to achieve? There may be another way to do it...

6 Posts

eelkevdbos Reply #3, 15 years, 2 months ago

$c->newQuery('Banner');

$c->select('Banner.*, SUM(IF(Banner.position = Clicks.posid, 1.0, 0)) as `clicks`, Position.name as `cur_position`, ROUND(SUM(IF(Banner.position = Clicks.posid, 1.0, 0))/SUM(IF(Banner.position = Clicks.posid, 1.0, 0))*100.0, 2.0) as `perc_position`');

$c->leftJoin('Click', 'Clicks')->leftJoin('Position', 'Position');

$c->groupby('Banner.id');

This is how I solved the problem, but I think it’s not a good thing that xPDO adds backticks on int’s other than zero.

MODX Staff
10,725 Posts

opengeek Reply #4, 15 years, 2 months ago

To send aliased select columns in xPDO 2.1+, use an associative array, where the keys represent the column alias, i.e.

<?php
$c->select($modx->getSelectColumns('Banner', 'Banner'));
$c->select(array(
    'clicks' => "SUM(IF(Banner.position = Clicks.posid, 1.0, 0))",
    'cur_position' => "Position.name"
    'perc_position' => "ROUND(SUM(IF(Banner.position = Clicks.posid, 1.0, 0))/SUM(IF(Banner.position = Clicks.posid, 1.0, 0))*100.0, 2.0)"
));

BTW and FWIW, doing a group by when selecting columns not in the group by will only work in MySQL, and does not work in MS SQL Server. It does work in SQLite, though it sorts in reverse before applying any grouping which can give you different results than MySQL with the same query.

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

6 Posts

eelkevdbos Reply #5, 15 years, 2 months ago

@OpenGeek Thanks, this solves my aliasing problem. So the values of the associative array’s aren’t converted into aliases.