Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Google Sign In with OpenID

Efficient Join Calls

edited May 2012 in Framework

I am totally blanking on this... is there an efficient JOIN call within the models class? I searched through the docs and didn't find much.

I have the relational "belongs_to" working, but from my understanding I would be hitting the database an additional time for every new call to said relationship (it returns a new result each time so it wouldn't be cached automatically by the framework).

For instance I have orders{id, user_id, date} and users{id, name}. What is the proper way using the models to call the join that would allow me to get all pertinent information in one call rather than abuse the relational model? Thanks!

Comments

  • You'll probably have to craft a custom query using something like this:

    <?php
    
    $q = User::query ('u.email, v.id, v.class, v.pkey, v.ts')
        ->from ('user u, versions v')
        ->where ('v.user = u.id');
    
    info ($q->sql ());
    info ($q->fetch_orig ());
    
    ?>
    

    In the context of your orders table, a belongs_to relationship is only meant to grab a single user, while the reverse has_many essentially does a select * from orders where user_id = ?. To do that, you can override the User model like this:

    <?php
    
    namespace myapp;
    
    // make sure User is loaded, since the autoloader
    // may find yours first
    require_once ('apps/user/models/User.php');
    
    class User extends \User {
        public $table = 'user';
    
        public $fields = array (
            'orders' => array (
                'has_many' => 'Order',
                'field_name' => 'user_id',
                'order_by' => array ('date', 'desc')
            )
        );
    }
    
    ?>
    

    Now your myapp\User class behaves exactly like the default User class, but with the added ->orders() to fetch all orders for a user. That's still 2 queries though, one for the user object, and one for the orders.

    If you want a single query to join the info for each record, I'd probably just add custom methods to the Order model:

    <?php
    
    class Order extends Model {
        public $table = 'orders';
    
        public $fields = array (
            'user' => array (
                'belongs_to' => 'User',
                'field_name' => 'user_id'
            )
        );
    
        public static by_user ($user_id) {
            return self::query ('user.*, orders.*')
                ->from ('user, orders')
                ->where ('orders.user_id = user.id')
                ->where ('user.id', $user_id)
                ->order ('order.date desc')
                ->fetch ();
        }
    }
    
    ?>
    

    That keeps it pretty clean:

    <?php
    
    $orders = Order::by_user ($user_id);
    
    ?>
    

    Hope that gives you some ideas :)

  • Gotcha. I'll play with it a bit see what I can figure out.

Sign In or Register to comment.