0

I have this especific query in hands and I want to write it as a cakephp query, but there are so much especific things that nothing that I tried worked to me:

select concat(c.name,' - ',e.name) as workplace 
from vacancies v, states e, citys c 
where c.idstate = e.id 
and v.idstate = c.idstate 
and v.idcity = c.id;

I'm really lost how to translate this query literally to cakephp style, kind of like:

$workplace = $this->Vacancies->Cities->find(...)->where(...);
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
Luís Fernando
  • 11
  • 3
  • Possible duplicate of [How to genereate SQL function calls with the CakePHP query builder?](http://stackoverflow.com/questions/30845997/how-to-genereate-sql-function-calls-with-the-cakephp-query-builder) – ndm May 09 '16 at 13:43
  • Please include examples of what you're tried. – Dave May 09 '16 at 14:02
  • I've tried things like: $workplace = $this->Vacancies->Cities->find('list')->select("concat(Citys.name,' - ',State.name)")->where(..don't worry with conditions..); mysql sql cakephp cakephp-3.0 – Luís Fernando May 09 '16 at 17:37

2 Answers2

2

You have obviously not made any attempt to read the official documentation.

Searching for "concat" would have shown this as the first hit. Let me copy and paste the manual for you:

CakePHP’s ORM offers abstraction for some commonly used SQL functions. Using the abstraction allows the ORM to select the platform specific implementation of the function you want. For example, concat is implemented differently in MySQL, PostgreSQL and SQL Server. Using the abstraction allows your code to be portable:

[...]

concat() Concatenate two values together. The arguments are treated as bound parameters unless marked as literal.

There is even an example included:

$query = $articles->find()->innerJoinWith('Categories');
$concat = $query->func()->concat([
    'Articles.title' => 'identifier',
    ' - CAT: ',
    'Categories.name' => 'identifier',
    ' - Age: ',
    '(DATEDIFF(NOW(), Articles.created))' => 'literal',
]);
$query->select(['link_title' => $concat]);
floriank
  • 25,546
  • 9
  • 42
  • 66
  • I tried it, but i just can't understand this last line "$query->select(['link_title' => $concat]);". What does it really do ?? – Luís Fernando May 10 '16 at 22:40
0

The last line is the alias 

concat(Articles.title, ' ', Categories.name) AS link_title
Isaac Palacio
  • 183
  • 3
  • 17