0

I have free tables: user, book, user_book, offers

user table has method:

public function getBooks()
    {
        return $this->hasMany(UserBook::className(), ['user_id' => 'id']);
    }

user_book table has two fields: user_id, book_id; and methods

public function getUser()
   {
        return $this->hasOne(User::className(), ['id' => 'user_id']);
   }

public function getBook()
   {
        return $this->hasOne(Book::className(), ['id' => 'book_id']);
   }

table offer have method like: getUser(), getBook(),

and now I would like show Books which user don't have. I try do something like

     $query = Offer::find()
                ->with('user')
                ->andWhere([
                    'offer.status' => Offer::STATUS_ACTIVE,
                ]);

$query->andWhere(['not in', 'offer.book_id', 'user.books.book_id']);

but it doesn't work. Do you have some ideas how can I make it?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Roboto6_1on
  • 315
  • 4
  • 20

1 Answers1

1

Yii2 docs, relation via junction table

In database modelling, when the multiplicity between two related tables is many-to-many, a junction table is usually introduced. For example, the order table and the item table may be related via a junction table named order_item. One order will then correspond to multiple order items, while one product item will also correspond to multiple order items.

When declaring such relations, you would call either via() or viaTable() to specify the junction table. The difference between via() and viaTable() is that the former specifies the junction table in terms of an existing relation name while the latter directly uses the junction table. For example,

class Order extends ActiveRecord
{
    public function getItems()
    {
        return $this->hasMany(Item::className(), ['id' => 'item_id'])
            ->viaTable('order_item', ['order_id' => 'id']);
    }
}
or alternatively,

class Order extends ActiveRecord
{
    public function getOrderItems()
    {
        return $this->hasMany(OrderItem::className(), ['order_id' => 'id']);
    }

    public function getItems()
    {
        return $this->hasMany(Item::className(), ['id' => 'item_id'])
            ->via('orderItems');
    }
}
Yupik
  • 4,932
  • 1
  • 12
  • 26
  • ok, I have `public function getBooks() { return $this->hasMany(Book::className(), ['id' => 'book_id']) ->viaTable('{{%UserBooks}}', ['user_id' => 'id']); }` but how to build query? – Roboto6_1on Feb 16 '17 at 14:45
  • You could just use `via('userBooks')` - u defined this relation as i can see. In query use `->joinWith(['books'])` and then in query use it as `books.id`, not `user.books.id` – Yupik Feb 16 '17 at 14:52