Akelos Framework v1 forum archive. This forum is no longer maintained. To report bugs please visit https://github.com/akelos/akelos/issues
    • CommentAuthorpogeybait4883

    I've posted several requests for help on this but can't seem to really get a good answer. If, in PHP, I want to create a query such as:

    SELECT comments.*,submission_title,flags FROM comments INNER JOIN ( SELECT registrations.id as registration_id, users.login as login, users.email FROM registrations INNER JOIN users ON registrations.user_id = users.id ) AS user ON comments.registration_id = user.registration_id INNER JOIN ( SELECT submissions.id as submission_id, submissions.something_id as submission_something_id, submissions.title as submission_title FROM submissions) as submission ON comments.submission_id = submission.submission_id LEFT JOIN ( SELECT comment_id as comment_flag_comment_id, COUNT(comment_id) as flags FROM comment_flags WHERE soft_delete=0 GROUP BY comment_id) as comment_flags ON comment_flags.comment_flag_comment_id = comments.id WHERE submission_something_id=5 ORDER BY comments.created_at DESC LIMIT 100

    No problemo. I stick that query in a good old $obj = mysql_fetch_object(mysql_query($sql)); and $obj contains all the records matching the query AND I get all the fields from comments, the submission_title field from the subquery, and the flags from the other subquery. All finished.

    But in Akelos this is a whole different story. If I do something in my akelos controller like:

    $this->comments =& $this->Comment->find( 'all', array( 'limit' => $this->sort_limit, 'offset' => $this->sort_offset, 'order' => $this->sort_order, 'conditions' => $conditions, 'joins' => $joins ));

    then I only get the fields from the comments table. Surely someone out there has created a view in the past and needed only certain fields from each of the tables that were joined and not ALL the fields.

    So I have no other choice but to use 'include' in my find() method and get ALL the fields from the joined tables. This is not an option for me because there would be over 8000 objects returned with a ton of extra data that I dont need. Processing time on this is so much that php times out.

    So what is the solution. How can I do the php query at the top where I can choose which fields I want returned? Please someone out there has to have been faced with this before and found a solution. Any help would be appreciated.

    Bermi or Kaste, any ideas please!?!?!


    If you're using the latest Akelos trunk version you can 'include' your associates using an alternative response on finders.

    You can get a nested array:

    $this->Comments = $this->Comment->find( 'all', array( 
        'include' => array('registrations', 'submissions', 'comment_flags'),
        'returns' => 'array'

    or a lightweight ActiveRecord:

        $this->Comments = $this->Comment->find( 'all', array( 
            'include' => array('registrations' => array('include' => 'users'), 'submissions', 'comment_flags'),
            'returns' => 'simulated'

    Implementing the missing 'select' option with the new include functionality will speed up data transfer from the database and save some CPU cycles.

    If you or someone is interested in helping me out with the select part, please clone the akelos git repository instead of the stable svn trunk. I'll be available at #akelos if you have any question.