Converting an SQL query to XPDO, stuck on JOIN with Select

85 Posts

mflammia Reply #1, 13 years, 8 months ago

Having a little trouble converting the following SQL into an XPDO statement

SELECT d . * 
FROM DestinationNames d
JOIN (
SELECT LENGTH( Number ) AS maxlen, number
FROM DestinationNames d
WHERE  '019235678910' LIKE CONCAT( Number,  '%' ) 
ORDER BY maxlen DESC 
LIMIT 1
)dsum ON d.Number = dsum.Number

I'm getting a little unstuck on the JOIN

Heres what I have so far:

$c = $modx->myDB->newQuery('DestinationNames');
$c->select(d . *);
$c->from(DestinationNames d);
$c->innerjoin(); ???

$c->select(LENGTH( Number ) AS maxlen, number);
$c->from(DestinationNames d)
$c->where( '019235678910' LIKE CONCAT( Number,  '%' ) );
$c->sortby(maxlen DESC );
$c->limit( 1);

????    )dsum ON d.Number = dsum.Number

Thanks in advance.

This question has been answered by mflammia. See the first response.

[ed. note: mflammia last edited this post 13 years, 8 months ago.]

85 Posts

mflammia Reply #2, 13 years, 8 months ago

So I think I might have been going about this wrong for a complex query and after reading this helpful article http://lazylegs.info/articles/modx-revolution-and-sql-(xpdo).html I have tried the following:

$query ='
	SELECT d . * 
        FROM DestinationNames d
           JOIN (
          SELECT LENGTH( Number ) AS maxlen, number
          FROM DestinationNames d
          WHERE  '019235678910' LIKE CONCAT( Number,  '%' ) 
          ORDER BY maxlen DESC 
          LIMIT 1
        )dsum ON d.Number = dsum.Number';

$criteria = new xPDOCriteria($modx, $query);

If ($results = $modx->myDB->getCollection('DestinationNames', $criteria)) {
        foreach ($results as $result) {
            $fields = $result->toArray();
            $rows[] = $modx->getChunk('tpl.ShowDataDestinationNames', $fields);
        }
 
        $ph['rows'] = implode('', $rows);
        $output = $modx->getChunk('tpl.ShowDataOuterDestinationNames', $ph);
 
    } else {
        $output = $noresults;
    }
}
return $output;

Problem is I am now getting the following error:

Parse error: syntax error, unexpected T_LNUMBER in /home/sites/domain.co.uk/public_html/nhs/core/cache/includes/elements/modsnippet/39.include.cache.php on line 42

Which seems to be pointing to the contents of $query.

I have run the SQL in PhpMyAdmin and it runs fine.

Any ideas - thanks.

discuss.answer

85 Posts

mflammia Reply #3, 13 years, 8 months ago

Sorted it, just needed to rap the the query in speak marks instead:

$query = "SELECT d . * 
    FROM DestinationNames d
    JOIN (
     SELECT LENGTH( Number ) AS maxlen, number
     FROM DestinationNames d
     WHERE  '019235678910' LIKE CONCAT( Number,  '%' ) 
     ORDER BY maxlen DESC 
     LIMIT 1
    )dsum ON d.Number = dsum.Number";

Answered Converting an SQL query to XPDO, stuck on JOIN with Select