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

General: findBySql only returns valid fields according to current model?

Bottom of Page

1 to 7 of 7

  1.  
    • CommentAuthorhaakon
     
    Hi,

    I am wondering how this function is supposed to be used.

    The documentation gives this example:
    $Post->findBySql("SELECT p.*, c.author FROM posts p, comments c WHERE p.id = c.post_id");

    But the problem is that findBySql only returns fieldnames that is valid inside "Post". So I would be unable to receive the author field in this example.

    How is this supposed to be done? Is this a bug? How can I do advanced sql queries? I want to do a lot of sum(),max(), etc in one query with group by. This works with this function, but only if i return with the original fieldnames.
    • CommentAuthorpogeybait4883
     

    If you need to include another table, use the include directive

    $posts =& $this->Post->find('all', array( 'conditions' => array('id=?', $id) 'include' => 'author' ));

    Then you get all the author table's fields. Of course your models for posts and authors must have relationships setup like:

    class Post extends ActiveRecord { var $has_many = array( 'Authors' => array( 'dependent' => 'destroy' )); }

    class Author extends ActiveRecord { var $belongs_to = array( 'Post' ); }

    The important thing to keep in mind with what you were trying is that you were using the findBySQL() method on the $Post model. Therefore you will only get fields back from the Post model.

    • CommentAuthorhaakon
     
    You seem to have missed the point. I know about relations.

    I want to do a lot of SUM() and COUNT(), etc on the data retreived. And in one sql, not do several sqls with $Post->max('field') etc..

    Say this is a CDR model for example, with thousands of rows, and I want to aggregate data in several ways for each row.

    I can do SUM(field) as field, and that works good. But if I need SUM(field > 0) as other field that is already used up.
    •  
      CommentAuthorbermi
     

    Are you aware of AkDbAdapter methods available on active records at ->db or by getting an adapter instance using Ak::db()?

    select
selectOne
selectValue
selectValues
    • CommentAuthorpogeybait4883
     
    Bermi,

    Can you show us a quick example of how to do those? I think this might answer some questions I had in the past about how to query one or possibly more tables using a generic query that isnt bound to an active record instance.

    Thanks.
    • CommentAuthorpogeybait4883
     

    Ah, figured it out. This is way cool!

    Here is an example. FYI I was able to do inner joins on other tables as well. This is pretty much a free-for-all query.

    This example is run in a controller.

    $this->adapter = Ak::db(); $this->all_records = $this->adapter->select('SELECT * FROM table WHERE field=1');

    Give you back a nice little assoc array (hash). Very very nice.

    Pretty sweet! Thanks Bermi.

    • CommentAuthorhaakon
     
    Thanks bermi, I was not aware of that :)

1 to 7 of 7

  1.  
Back to Discussions Top of Page