What is a more efficient query for this many to many eloquent query?-CodePudding

There are the following tables: lists, items, and the pivot table items_to_lists, which has the columns list_id and items_id.

I know the list_id and account_id and I need to get all the items (with specific columns) that belong to it and belong to the account (there is accounts table),

I currently tried the following:

$items = Lists::with(['items' => function ($query) {
        $query->select('column1', 'column2');
    }])->find(3);

But it looks like it's not efficient because it also fetches the pivot table data with every item:

"items": [
{
    "id": 1,
    "name": "Item Name",
    "pivot": {
        "id"..
        "list_id"..
        "item_id"..
    }
},
// ..

What query is better for this purpose? Is it more close to a raw SQL, or I can still make this eloquent efficient and choose only the needed data (without the pivot)?

CodePudding user response：

Eloquent must have the pivot data. If you don't remove the topic_id from the selected columns, no results will be returned because of the way it associates the data.

Assuming you have a pivot model for items_to_lists, you could create a HasMany relationship between List and ItemToList (or however you want to call that model) and then customize the query so it joins to your Item model.

class ItemToList extends Pivot
{
    protected $table = 'items_to_lists';
}

class List extends Model
{
    ...
    public function item_to_list()
    {
        return $this->hasMany(ItemToList::class, 'item_id');
    }
}

$items = Lists::query()
    ->with(['item_to_list' => function ($query) {
        $query->select('i.name', 'list_id', 'item_id')
              ->join('items as i', 'i.id', 'item_id');
    }])
    ->find(3);

For comparison, your original query creates the following SQL queries:

-- Query 1
select ...
from "lists" where "lists"."id" = 3
limit 1

-- Query 2
select
    "items"."name",
    "items_to_lists"."list_id" as "pivot_list_id",
    "items_to_lists"."item_id" as "pivot_item_id"
from "items"
inner join "items_to_lists" on "items"."id" = "items_to_lists"."item_id"
where "items_to_lists"."topic_id" in (3)"

And the alternative I wrote generates the following queries

-- Query 1
select ...
from "lists" where "lists"."id" = 3
limit 1

-- Query 2
select
    "i"."name",
    "list_id",
    "item_id"
from "items_to_lists"
inner join "items" as "i" on "item_id" = "i"."id"
where "items_to_lists"."list_id" in (3)

As you can see, the queries are pretty much the same.