SQL query to cakePHP format (invalid json result)

Question

Hello I have a PostgreSQL query that I would like to write using cakePHP format

SELECT 
  id, 
  title, 
  author, 
  postdate, 
  postcontent, 
  userID 
FROM posts 
WHERE 
  userID = 12 
  AND id IN (SELECT articleID FROM favourites WHERE articlesUserID = 12) 
ORDER BY postdate DESC;

this is the format my query has right now in cakePHP :

$favouritearticles = $this->Favourite->query('SELECT id, title, author, postdate, postdatecreation, posteditdate, postcontent, "userID" FROM posts WHERE "userID" = '.$userID.'AND id IN (SELECT lngblogarticleid FROM favourites WHERE lngloginuserid = '.$userID.') ORDER BY postdate DESC');

It's working but if echo json_encode the result like this :

echo json_encode($favouritearticles);

I get an invalid json format like the following :(checked with JSONLint)

[
    [
        {
            "id": 2,
            "title": "Prison Or Treatment For the Mentally ill ",
            "author": "mike123",
            "postdate": "March 12, 2013 at 6:46 pm",
            "postdatecreation": "2013-03-12",
            "posteditdate": null,
            "postcontent": "<p><span>The public revulsion over repeated mass shootings has placed mental health in the spotlight. This is both good and bad.<\/span><\/p>",
            "userID": 34
        }
    ]
][

]

So I thought that maybe I should rewrite my query using cakePHP format "using find method" something like :

$favouritearticles = $this->Favourite->find('all',array('conditions'=>array(".........

however the query is quite complex and I don't see how to do so. Thank you for any help.

What have you tried? Please show what you've already attempted and explain where you got stuck. — Craig Ringer, Mar 24 '13 at 04:45

score 1 · Answer 1 · answered Mar 24 '13 at 13:51

Format of JSON is fine except for extra [ ] at the end. If you still want to rewrite the query in CakePHP format, use following:

private function getFavouritePostsByUserId($userId) {
    $db = $this->Post->getDataSource();
    $subQuery = $db->buildStatement(
        array(
            'fields' => array('Favourite.articleID'),
            'table' => $db->fullTableName($this->Favourite),
            'alias' => 'Favourite',
            'conditions' => array(
                'Favourite.articlesUserID' => $userId
            ),
        ),
        $this->Favourite
    );
    $subQuery = 'Post.id IN (' . $subQuery . ') ';
    $subQueryExpression = $db->expression($subQuery);
    $conditions = array($subQueryExpression, 'Post.userID' => $userId);
    $fields = array('Post.*');
    $order = 'Post.postdate DESC';
    $this->Post->find('all', compact('conditions', 'fields', 'order'));
}

Hello, thanks for the answer, I tried your query format but it gave me an error : Error: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "favourite" LINE 1: ...lic"."posts" AS "Post" WHERE Post.id IN (SELECT Favourite.... ^ How can I simply remove that extra [] from the returned json (or before it was sent by the json_encoded()). Thanks. — OussamaLord, Mar 24 '13 at 14:29
Replace 'table' => $db->fullTableName($this->Favourite), with the name of your favourites table like this: 'table' => 'favourites', — Karisters, Mar 24 '13 at 14:49

SQL query to cakePHP format (invalid json result)

1 Answers1