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

    The issue I am having goes back to other posts I have made about doing a find(), findBySQL(), or other method that returns an object of a model. I still am having issues figuring out how to join multiple tables together AND get a select number of columns from each table. Let me start by showing an example of what I am trying to accomplish in my view. Since we cannot post HTML here, here is a text version of my view. The column headers are sortable:

    Submission Username Comment Active Flags Posted Actions

    Submission1 user1 This is a test Yes 0 1-1-09 Edit | Delete
    Submission2 user2 This is a test No 0 1-2-09 Edit | Delete
    Submission3 user3 This is a test Yes 2 1-3-09 Edit | Delete

    Showing page 1 of 5
    1 2 3 4 5 next

    Okay, you get the picture. I have accomplished making this view and getting the data from multiple tables but it comes at some cost in the way of processing time. It is very intensive. The Submission data comes from the submissions table, the Username data comes from users, Comment comes from comments, Active comes from comments, Flags comes from comment_flags, Posted comes from comments, and the actions are just links. To give you an idea of what is in each table, here are the important snippets from each installer/model. Forgive the "something" table in the model. This code comes from an active site and I dont want to give away what it is really for:

    /app/installers/comment_installer.php $this->createTable('comments', " id, registration_id, submission_id, comment text, active bool "); /app/models/comment.php var $belongs_to = array('Registration','Submission'); var $has_many = array( 'CommentFlags' => array( 'dependent' => 'destroy' ) ); /app/installers/submission_installer.php $this->createTable('submissions', " id, something_id int, registration_id int, title, description, active bool, "); /apps/models/submission.php var $belongs_to = array('Something','Registration'); var $has_many = array( 'Comments' => array( 'dependent' => 'destroy' ), ); /apps/installers/registration_installer.php $this->createTable('registrations', " id, user_id, something_id"); /apps/models/registration.php var $belongs_to = array('Something','User'); var $has_many = array( 'Submissions' => array('dependent' => 'destroy'), 'Comments' => array('dependent'=>'destroy'), 'CommentFlags' => array('dependent'=>'destroy'), ); /apps/installers/comment_flag_installer.php $this->createTable('comment_flags', " id, comment_id, registration_id "); /apps/models/comment_flag.php var $belongs_to = array('Comment','Registration'); /apps/installers/user_installer.php #no installer. Uses built-in user object /apps/models/user.php var $has_many = array( 'Registrations' => array( 'dependent' => 'destroy' ), 'Somethings' => array( 'dependent' => 'destroy' ), );

    Okay. Now you can see how I setup all my models. Here's where it gets interesting. In order to show the comment view as I described at the very top of this page, I do the following in the comment_controller's listing() action like this:

    function listing() {
        $conditions = 'submission_something_id=' . $this->CurrentSomething->id;
        $joins = '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';
        $this->comment_pages = $this->pagination_helper->getPaginator($this->Comment, array(
            'items_per_page' => $this->sort_limit,
            'count_conditions' => $conditions,
            'count_joins' => $joins
        $this->comments =& $this->Comment->find( 'all', array(
            'limit' => $this->sort_limit,
            'offset' => $this->sort_offset,
            'order' => $this->sort_order,
            'conditions' => $conditions,
            'joins' => $joins

    As you can see I had to write a pretty elaborate join statement. Most of the aliases that are created in the $joins statement are to allow me to use the alias names with the Akelos sorting feature like so: <%= sortable_link 'submission_title', {}, :link_text => _('Submission') %>

    But here is where I am having the issue. As you can see in my listing action, I am using $this->Comment->find() to execute the query. This means in my view I can use the $comments object to access any field in the comments table. But even though I used INNER JOIN and LEFT JOIN on other tables in the query, I am unable to access any of those columns from other tables such as $comment->submission_title.

    So in order to get information from the other tables in my view, I ended up using something like this: {?comments} {loop comments} <? $comment->submission->load(); $comment->registration->load(); $comment->registration->user->load(); $flags = $comment->comment_flag->count(); ?> ....show the info {end} {end}

    So lets recap. In the comment_controller listing() action, I run the expensive query to get an accurate record count for the paginator. I then run the expensive query again to build a $comments object with all my comments from the comments table, using INNER and LEFT joins on the other tables but none of the columns from those tables are included in my $comments object. So in the view I have to loop through each comment, load the submission for that comment, load the registration for that comment, load the user for that registration, and finally load the comment_flag count for the comment. This is fine if my database only has around 1 to 100 comments but what if I have, lets say, around 8000. It takes considerable time to do all of this redundant querying.

    In the past I've been told to try using findBySQL(), but you have to use that method on a model such as $this->comments =& $this->Comment->findBySQL(). Again, we are only going to get columns from the comments table.

    I have also been advised to use the 'include' directive to include those other table's columns in my query and then be able to access the other table's columns. Unfortunately as you can see, I'd have to include about 5 other tables. When doing that on 8000 comments, it would return a huge object with all the columns from each table whereas I only need a few columns. Plus I am not even sure I could use the SQL COUNT() function which would be necessary to count the comment_flags.

    So as you can see I am having some growing pains when trying to figure out how to do things the rails way. I am a solid PHP programmer and running the sql query with the joins in straight up PHP would be very simple and I could very easily use mysql_fetch_object() to get an object with the columns that I wanted.

    Any advise on how to accomplish this using the correct Akelos/Rails approach would be very much appreciated. Im running out of options. Bermi?