CakePHP 2.2.4: Why does this Union $this->Model->query not work?

Question

I'm in the view action of my PhotosController.php. What I want to do is given the id of the current photo I am viewing, create a carousel of photos containing the two photos before and two photos after the current photo with the current photo in the middle (5 in total).

I was pointed to this solution but I can't seem to convert it to CakePHP using $this->Photo->query.

My controller

$this->set('photos', $this->Photo->query("
    SELECT id, file FROM photos WHERE id <= $id AND page_id = $page_id ORDER BY id DESC LIMIT 3 
    UNION ALL
    SELECT id, file FROM photos WHERE id > $id AND page_id = $page_id ORDER BY id ASC LIMIT 2
"));

Unfortunately, when I don't see anything when I turn debugging on. id, file, and page_id are all columns in the photos table. Both #id and $page_id are passed to the action from the router. Is my syntax wrong?

EDIT: If I remove the UNION ALL and the second SELECT statement, then the query works fine so it's not an issue with the model not being loaded because it is.

EDIT (workaround): For now I'm doing two queries which is not ideal.

$this->set('photos_before', $this->Photo->find('all', array(
    'conditions' => array(
        'Photo.page_id' => $page_id,
        'Photo.id <' => $id
    ),
    'order' => array('Photo.id ASC'),
    'limit' => 2
)));

$this->set('photos_after', $this->Photo->find('all', array(
    'conditions' => array(
        'Photo.page_id' => $page_id,
        'Photo.id >' => $id
    ),
    'order' => array('Photo.id ASC'),
    'limit' => 2
)));

I have a contain before hand to only return the fields and associated models I need.

Below is what I want to be displayed and it currently works using the two queries above but I am hoping this can be achieved with a single, Cake-friendly query

This what I want to be displayed and it currently works using the two queries above but I am hoping this can be achieved with a single, Cake-friendly query

Yes Photo is a model. There doesn't seem to be a an error when the query is executed as the rest of my view is rendered, only the section containing the query says "Internal Error Occurred" — bowlerae, Feb 22 '13 at 14:43
If you enable debugging, what is the generated SQL? Have you tried to run that SQL inside MySQL (e.g. via PhpMyAdmin). Also, you're using the low-level 'query' method, which does NOT handle any escaping, *so please be very careful to prevent SQL-injection!* — thaJeztah, Feb 22 '13 at 15:06

score 2 · Accepted Answer · answered Feb 22 '13 at 21:16

2

My guess is that your original query is invalid SQL. Afaik UNIONS cannot contain multiple 'order by' clauses. As a workaround you may consider to rewrite it to use subqueries like this:

SELECT * FROM (SELECT id, file FROM photos WHERE id <= $id AND page_id = $page_id ORDER BY id DESC LIMIT 3) AS suba
UNION ALL
SELECT * FROM (SELECT id, file FROM photos WHERE id > $id AND page_id = $page_id ORDER BY id ASC LIMIT 2) AS subb

Although I serious think a query like this is far from optimal. Of course, I don't know the way your application works, but it seems that a standard pagination query, with a OFFSET/LIMIT is a more logical approach.

Please take my comment below your question into account; using model->query does NOT automatically handle sanitisation/escaping to prevent SQL injections!

answered Feb 22 '13 at 21:16

thaJeztah

27,738
9
73
92

This query works, the only issue is says I'm view Photo.id = 71, with the query as is the results are displayed with this order of IDs: 71, 70, 69, 72, 73. If I change "suba" to ORDER BY id ASC, the order of IDs is: 1, 2, 3, 72, 73. The first example contains the correct IDs but the incorrect order. I'm looking for this order: 69, 70, 71, 72, 73. Please see my edit "workaround". It displays the correct IDs in the correct order but it is two separate queries. – bowlerae Feb 22 '13 at 22:03
Ok so AFTER the "subb", I added a ORDER BY id ASC to the entire union and it works as expected. Your opinion, should I go with this->Photo->query or my two separate queries? Which would be better for performance? – bowlerae Feb 22 '13 at 22:08
I would try to achieve this without this kind of complex queries. I see the screenshot you've added and my guess is that you want to show 5 pictures per 'show'. This would be something like; show -> hasMany->pictures. Then select the show and paginate the pictures, with a 'page' size of 5? – thaJeztah Feb 22 '13 at 22:41
I have Page (show, but also other entertainment categories) hasmany Photo, which has many Comment but your suggestion would not work with my application. I'm not displaying 5 Photos per Page but instead up to 5 Photos per Photo. I'm also not sure if it matters but I already have paginated comments under the photo thumbnails. Not sure if you can have two different paginated lists on the same view? – bowlerae Feb 23 '13 at 06:07
The screenshot you're showing, is it of a 'page' (e.g. Somebody selected the 'dexter' page, or is it just showing 'any' photo? Are the 5 photos 'related' in any way? And if not, how has the 'main' photo been selected? E.g. Has the main photo been selected from a 'list' or via the 'show'? – thaJeztah Feb 23 '13 at 09:11
The screenshot is of the PhotosController for Page.id = 2. $page_id is passed to the PhotosController view() method along with other variables one of which is the $id of the photo. Photo model does not have any relation to itself as in no Photo hasmany Photo, etc (maybe I should do something like that?). The only way these photos are "related" is because they all have the same page_id = 2 and the same album_id. Page hasmany [Photo, PhotoAlbum]. PhotoAlbum hasmany Photo. Photo belongsto [Page, PhotoAlbum] – bowlerae Feb 23 '13 at 17:26

score 0 · Answer 2 · answered Feb 22 '13 at 14:29

0

You have to load model as

  $this->loadModel('Photo');

Before executing query.

answered Feb 22 '13 at 14:29

sandip

3,279
5
31
54

I have to do this even though I'm INSIDE of the PhotoController which by default already uses the Photo model? – bowlerae Feb 22 '13 at 14:43
Actually, I even have `var $uses = array('Photo', 'PhotoSetting');` at the top of my page so the model is already loaded. – bowlerae Feb 22 '13 at 14:44

score 0 · Answer 3 · answered Feb 22 '13 at 18:46

0

You should create a VIEW in MySQL and then use that as a model, and do a traditional CakePHP find on that.

Read up on creating views in MySQL and then create a model based on that view name.

answered Feb 22 '13 at 18:46

Barry Chapman

6,690
3
36
64

Could you please post the link to the associated Cook Book entry or other guide? Thanks – bowlerae Feb 22 '13 at 19:31

CakePHP 2.2.4: Why does this Union $this->Model->query not work?

3 Answers3