如何在我的模型中通过关系表多对多改进搜索？

Just for simplify my problem, let's say i have a project for a library system that implements all features for borrow, count, search, and add books in my library.

为了简化我的问题，假设我有一个图书馆系统项目，它实现了借阅，计数，搜索和在我的图书馆中添加图书的所有功能。

For that project i made 3 main tables: person (id, name, etc), loan (id, id_person, dates, etc) and, of course, book (id, title, etc).

对于该项目，我制作了3个主要表：人（id，姓名等），贷款（id，id_person，日期等），当然还有书（id，title等）。

Since a Person can borrow more than one book at same time, i also needed another table just to link this many-to-many relationship. rel_loan_book (id, id_book, id_loan).

由于一个人可以同时借阅多本书，我还需要另一张表来链接这种多对多的关系。 rel_loan_book（id，id_book，id_loan）。

Now, in my Book View i have a Gridview with all information of the book and a column saying whether the book is available for borrow or not (not being used at the moment).

现在，在我的Book View中，我有一个Gridview，其中包含该书的所有信息以及一个列，说明该书是否可以借用（目前尚未使用）。

Here is what i'm doing at the moment:

这就是我现在正在做的事情：

View:

视图：

[
    'attribute' => 'isAvaliable',
    'value' => function($model) {
        return $model->currentLoan ? 'No' :'Yes';
    },
    'filter' => Html::activeDropDownList($searchModel, 'isAvaliable', ['1' => 'Yes', '0' => 'No'],
        ['class'=>'form-control','prompt' => '']
    )
],

Model Book:

型号书：

public function getCurrentLoan()
{
     return $this->hasOne(Loan::className(), ['id' => 'id_loan'])
         ->viaTable(RelLoanBook::tableName(), ['id_book' => 'id'])
         ->onCondition(['loan.status' => 'A']);
}

Status 'A' in Loan table means it's still active (the book didn't came back).

贷款表中的状态“A”表示它仍然有效（该书没有回来）。

My problem is when i try to make a search by available or not available books... At the moment, i'm doing another query just to check what are the borrowed books and then removing (or filtering by) this ids in my search:

我的问题是当我尝试通过可用或不可用的书籍进行搜索时...目前，我正在进行另一个查询，只是为了检查借来的书籍是什么，然后在我的搜索中删除（或过滤）此ID ：

BookSearch:

的BookSearch：

public $isAvaliable;

...

public function rules()
{
    return [
        [['isAvaliable'], 'safe'],
    ];
}

...

public function search($params)
{
    ...

    if ($this->isAvaliable !== '') {
        $subQuery = Book::find()->select('book.id');
        $subQuery->joinWith(['currentLoan'])
            ->andWhere(['is not' , 'loan.id', null])
            ->all();

        if ($this->isAvaliable === '1') {
            $query->andWhere(['not in', 'book.id', $subQuery]);
        } elseif ($this->isAvaliable === '0') {
            $query->andWhere(['in', 'book.id', $subQuery]);
        }
    }

    ...
}

I don't think this is the best approach for that. But i can't think in a sql query making that search without a subquery:

我不认为这是最好的方法。但我不能在没有子查询的情况下在sql查询中进行搜索：

SELECT * FROM book
LEFT JOIN rel_loan_book ON rel_loan_book.book_id = book.id
LEFT JOIN loan ON loan.id = rel_loan_book.loan.id
WHERE ( /* my filters */)
AND ( /* some restriction that check if the book does OR does not have a loan with status === 'A' */)

1 个解决方案

#1

First off, do you have a reason for not making loan the junction table? This seems more logical to me and would simplify your query.

首先，你有没有理由不贷款联络表？这对我来说似乎更合乎逻辑，并且会简化您的查询。

I.e.:

即：

person(id, name)
人（身份证，姓名）
book(id, name, author, isbn, publisher, genre, tags)
书（id，name，author，isbn，publisher，genre，tags）
loan(id, person_id, book_id, start_date, due_date, return_date, status)
loan（id，person_id，book_id，start_date，due_date，return_date，status）

By connecting book and loan the system is more flexible, allowing for:

通过连接账簿和贷款，系统更加灵活，允许：

loan extensions on some of the titles picked up
一些标题的贷款延期被提升
early returns for some of the items
一些项目的早期回报

It would be weird if a library would refuse the return of all books if you forgot one of them. That only happens when they are all connected to a single loan.

如果你忘了其中一本书，如果图书馆拒绝归还所有书籍，那就太奇怪了。只有当它们都连接到一笔贷款时才会发生这种情况。

After a little structural revision, add this to book model:

稍作结构修改后，将其添加到图书模型中：

public function getIsAvailable()
{
    return !Loan::find()->where([
        'book_id' => $this->id,
        'status' => 'A'
    ])->exists();
}

If a loan record with an active status and this book id exists, then its not available.

如果存在具有活动状态和此书ID的贷款记录，则其不可用。

Add isAvailable attribute to the search filter:

将isAvailable属性添加到搜索过滤器：

 $query->andFilterWhere([
     ...
     'isAvailable' => $this->isAvailable,
 ]);

Links the getIsAvailable function with the dropdown filter.

将getIsAvailable函数与下拉过滤器链接。

Adjust isAvailable column in book-view:

在book-view中调整isAvailable列：

[
    'attribute' => 'isAvailable',
    'value' => function($model) {
        return $model->isAvailable ? 'Yes' :'No';
    },
    'filter' => Html::activeDropDownList($searchModel, 'isAvailable', 
        [0 => 'No', 1 => 'Yes'],
        ['class'=>'form-control','prompt' => '']
    )
],

#1