Datamapper Query questions

[eluser]mikeymayhem[/eluser]
Hi there,

i have been using CI for a almost a year now and have recently discovered the amazing world of datamapper. I have recently undertaken a pretty huge project and decided that datamapper would be perfect in helping me master techniques i currently dont know an aweful lot about, that said there may be a few questions being posted as i delve into this amazing ORM. Id like to say a huge thanks to anyone that takes the time to help me with this problem!!
right... i have spent the last week or so reading everything i could find on datamapper and have now started on my project. I have outlined what im trying to achive pretty basically below to save on boring explinations but here goes!

Code:

$g = new Gig();
$g->select('id','headline');
$g->include_related('venue', 'name');
$g->include_related('flyer','id');
$g->include_related('user','username');
$g->where('status','active');
$g->get();

Code:

$g->comment->where_related('gig','id',$g->id)->get();
$g->band->where_related('gig','id',$g->id)->get();

at the moment this will run 3 queries one accessing all the gigs and joining the has_one relationships with the gigs venue, and the flyer for said gig, it then runs another query to grab all the comments for that gig and then again to grab all the bands. as i said this is pretty basic, im just planning stuff out atm really and seeing whats possible.

what i want to know is can all this be achived in one query?? im probably missing someting quite obv here and have tried out what i thought would work by have had no luck. The big problem is i will have lots of gigs so when it loops through each gig and grabs that gigs comments and bands doing 3 quries each time could become a problem. Sorry if this is a bit long winded for such a small request! Thanks again for any help!!

[eluser]centurionas[/eluser]
In my opinion, any ORM might help you to save a lot of time on simple queries but no ORM will hit the best performance on more complex sql queries, when compared to pure sql statements.

[eluser]WanWizard[/eluser]
That all depends on the queries generated, and on the circumstances you're in.

In the case of Datamapper, the queries generated are performant, in all projects I've used it in, I've only seen one query I would have coded differently.

When it comes to performance, power is cheap these days, an extra (powerful) server is cheaper than a weeks wages of a good developer. From a clients point of view, an ORM mean faster development means a less costly result.


@mikeymayhem,

Datamapper generates the query in the same sequence as the methods you use. Use $g->check_last-query() to see what query has been produced, determine what you would like to optimize, and modify your statement accordingly.

[eluser]mikeymayhem[/eluser]
Thanks for the replies guys!! I already had check_last_query() running, great little tool!! i also have profiler turned on so that helping me to monitor what quiries are being run too!

I dont know any aweful lot about all this stuff as i already said but here are the quiries being run - my point is it would have to do all this for each gig so a foreach looping through all the active gigs then inside that foreach it would pull the gigs comments and bands. is this going to be to resource intensive or is it not really a probelm performance wise... is there are way to get datamapper to build it all in one query, would that actually cost lest in resources??

Code:

SELECT `gigs`.*, `venues`.`name` AS venue_name, `venues`.`address_1` AS venue_address_1,
    `venues`.`address_2` AS venue_address_2, `venues`.`city` AS venue_city, `venues`.`area` AS
    venue_area, `venues`.`post_code` AS venue_post_code, `venues`.`created` AS venue_created,
    `venues`.`updated` AS venue_updated, `flyers`.`img_url` AS flyer_img_url, `flyers`.`img_name` AS
    flyer_img_name, `flyers`.`img_width` AS flyer_img_width, `flyers`.`img_height` AS flyer_img_height,
    `flyers`.`img_size` AS flyer_img_size, `flyers`.`status` AS flyer_status, `users`.`username` AS
    user_username
FROM (`gigs`)
LEFT OUTER JOIN `venues` venues ON `venues`.`id` =
    `gigs`.`venue_id`
LEFT OUTER JOIN `flyers` flyers ON `flyers`.`id` = `gigs`.`flyer_id`
LEFT OUTER
    JOIN `users` users ON `users`.`id` = `gigs`.`user_id`
WHERE `gigs`.`status` = 'active'
SELECT `gigs`.*, `venues`.`name` AS venue_name, `venues`.`address_1` AS venue_address_1,
    `venues`.`address_2` AS venue_address_2, `venues`.`city` AS venue_city, `venues`.`area` AS
    venue_area, `venues`.`post_code` AS venue_post_code, `venues`.`created` AS venue_created,
    `venues`.`updated` AS venue_updated, `flyers`.`img_url` AS flyer_img_url, `flyers`.`img_name` AS
    flyer_img_name, `flyers`.`img_width` AS flyer_img_width, `flyers`.`img_height` AS flyer_img_height,
    `flyers`.`img_size` AS flyer_img_size, `flyers`.`status` AS flyer_status, `users`.`username` AS
    user_username
FROM (`gigs`)
LEFT OUTER JOIN `venues` venues ON `venues`.`id` =
    `gigs`.`venue_id`
LEFT OUTER JOIN `flyers` flyers ON `flyers`.`id` = `gigs`.`flyer_id`
LEFT OUTER
    JOIN `users` users ON `users`.`id` = `gigs`.`user_id`
WHERE `gigs`.`status` = 'active'


SELECT `comments`.*
FROM (`comments`)
LEFT OUTER JOIN `comments_gigs` comments_gigs ON
    `comments`.`id` = `comments_gigs`.`comment_id`
WHERE (
`comments_gigs`.`gig_id` = 1
)
AND
    `comments_gigs`.`gig_id` = 1
SELECT `comments`.*
FROM (`comments`)
LEFT OUTER JOIN `comments_gigs` comments_gigs ON
    `comments`.`id` = `comments_gigs`.`comment_id`
WHERE (
`comments_gigs`.`gig_id` = 1
)
AND
    `comments_gigs`.`gig_id` = 1


SELECT `bands`.*
FROM (`bands`)
LEFT OUTER JOIN `bands_gigs` bands_gigs ON `bands`.`id` =
    `bands_gigs`.`band_id`
WHERE (
`bands_gigs`.`gig_id` = 1
)
AND `bands_gigs`.`gig_id` = 1
SELECT `bands`.*
FROM (`bands`)
LEFT OUTER JOIN `bands_gigs` bands_gigs ON `bands`.`id` =
    `bands_gigs`.`band_id`
WHERE (
`bands_gigs`.`gig_id` = 1
)
AND `bands_gigs`.`gig_id` = 1

[eluser]WanWizard[/eluser]
Assuming 'comments' and 'bands' have a one-to-many with gig, why don't you include those as well?

[eluser]mikeymayhem[/eluser]

Code:

-----------------|-----------------|---------------|
gigs             |bands_gigs       |bands          |
-----------------|-----------------|---------------|
id               |id               |id             |
user_id          |band_id          |band_name      |
flyer_id         |gig_id           |               |
venue_id         |                 |               |
----------------------------------------------------
-----------------|-----------------|---------------|
gigs             |comments_gigs    |comments       |
-----------------|-----------------|---------------|
id               |id               |id             |
user_id          |comment_id       |user_id        |
flyer_id         |gig_id           |title          |
venue_id         |                 |comment        |
----------------------------------------------------

Hey thanks for the reply again, thought it might help if i layed out my database (roughly) to show why i may not be getting the results. Normilization is pretty new to me but this is how i interpreted the best way to achive my results using datamapper. if i try to include_related on bands and comments i get the errors saying the dont have the right relationships? im really keen to use datamapper so any advice you could give to help me out here would be greatly appreciated!!

[eluser]WanWizard[/eluser]
Can you post the relationship definitions for the gig, band and comment models? Combined with the table layouts I can setup a test here...

[eluser]mikeymayhem[/eluser]
Ah ledge!! im sure its something simple that i have not understood properly but that's the learning process ah!! Thanks so much for your help!!

Code:

<?php
class Gig extends DataMapper
{
    var $has_one = array('venue','flyer','user');
    var $has_many = array('comment','band');

    function  __construct()
    {
        parent::__construct();
    }

}
/* End of file gig.php */
/* Location: ./application/models/gig.php */

<?php
class Comment extends DataMapper
{
    var $has_one = array('user');
    var $has_many = array('post','gig');

    function  __construct()
    {
        parent::__construct();
    }

}
/* End of file comment.php */
/* Location: ./application/models/comment.php */

<?php
class Band extends DataMapper
{
    var $has_one = array();
    var $has_many = array('gig','user');

    function  __construct()
    {
        parent::__construct();
    }

}
/* End of file band.php */
/* Location: ./application/models/band.php */

<?php
class Flyer extends DataMapper
{
    var $has_one = array('gig');
    var $has_many = array();

    function  __construct()
    {
        parent::__construct();
    }

}
/* End of file flyer.php */
/* Location: ./application/models/flyer.php */

<?php
class User extends DataMapper
{
    var $has_one = array();
    var $has_many = array('post','comment');

    var $validation =  array(
        'username' => array(
            'label' => 'Username',
            'rules' => array('required', 'trim', 'max_length' => 60, 'min_length' => 3, 'unique')
         ),
        'password' => array(
            'label' => 'Password',
            'rules' => array('required', 'trim', 'encrypt')
        ),
    );

    function  __construct()
    {
        parent::__Construct();
    }

    
    /**
     * @access private
     * @author Mike Waites 19.12.10
     * @uses md5 form input data before submitting to db
     */
    function _encrypt()
    {
        //dont encrypt an empty field
        if( !empty($field) )
        {
            //md5 hash the db field
            $this->{$field} = md5($this->{$field});
        }
    }
}
/* End of file user.php */
/* Location: ./application/models/user.php */

<?php
class Post extends DataMapper
{
    var $has_one = array('user');
    var $has_many = array('comment',);

    function  __construct()
    {
        parent::__construct();
    }
    
}
/* End of file gig.php */
/* Location: ./application/models/gig.php */

<?php
class Venue extends DataMapper
{
    var $has_one = array('gig');
    var $has_many = array();

    function  __construct()
    {
        parent::__construct();
    }

}
/* End of file news.php */
/* Location: ./application/models/news.php */

[eluser]WanWizard[/eluser]
include_related() only works on 'has_one' relationships, and both 'band' and 'comment' are many-to-many relations. You can not include the result of many records into one result record (as your gig query returns one record per gig).
So you will have to use 3 queries to get all information.

At the moment, you also have to run the queries in a foreach, as the 'id' of the selected gig ( $g->id ) is used to run the subsequent queries. I think it's possible to add a method to do a 'get_all()'. If you want that feature, please add a feature request here.

As a workaround:

Code:

// create the Gig object
$g = new Gig();

// get all gig's ( you can use your original gig query here)
$g->get();

// create an array with gig id's in scope
$arr = array();
foreach ($g as $gig)
{
    $arr[] = $gig->id;
}

// fetch all comments for the gig's in scope
$c = new Comment();
$c->where_in_related('gig', 'id', $arr)->get();

and a tip: you have to define the constructor with a parameter 'id', otherwise you can't create a new object using "new Object($id);"

Code:

function __construct($id = NULL)
{
    parent::__construct($id);
}

[eluser]mikeymayhem[/eluser]
Thanks for getting back to me again! Ill give that a go.... was thinking maybe it would be easier if i created a table for each type of comment, so comment_gigs would actually contain just the specific gig comments data? that would make what im trying to achive a bit easier right??