How can I fetch this info in laravel Eloquent

Question

Sorry for the question title but I can't really find an suitible title.

I use Laravel 3 with Eloquent models (first project in Laravel). I have an user, list and item model. The user can't fetch any lists, the list can fetch items. But the user can order the items, so the order is saved per user/item.

Will be more clear with following data (simplified for clarity):

Database table:

USER
--------
id
name

LIST
-------
id
name

ITEM
-------
id
list_id
name

USER_ITEM_ORDER
---------------
user_id
item_id
order

List model:

class List extends Eloquent {
    public function items() {
        return $this->has_many('Item');
    }
}

Now I want to use the list model to get all the items based on an user, $list->items($user_id) --> array with items with the user's order.

Can someone show me the way to achieve this?

Answer 1

Yeah! I fixed it. :D I'll answer my own question because there is an up vote..

It was pretty easy, just use Fluent on the returned object like this:

public function items($user_id = 0) {
    $items = $this->has_many('Item');

    // do we need to get the order?
    if($user_id != 0) {
        $items = $items->left_join('user_item_order', 'items.id', '=', 'user_item_order.item_id')
                       ->where('user_item_order.user_id', '=', $user_id)
                       ->order_by('user_item_order.order', 'asc');
    }

    return $items->get();
}

Answer 2

Please try using this code :

public static function NAMEUserId() {
        $query = DB::table('username')
        ->select(array('users.username', DB::raw('NAME(user's.id) as UserID')))
        ->join('users', 'items.user_id', '=', 'users.id')
        ->group_by('users.id')
        ->order_by('usersid', 'desc')
        ->get();
    return $query;
}