How to query dataset with exactly specified relations in Laravel?

Published

I have the following situation:

A table product_mappings and a table product_mapping_options. I now want to find all entries within all product_mappings to which an entry product_mapping_options with certain ids is assigned. For example, this could look like this:

      "data" => array:4 [
        0 => array:6 [
          "id" => 4
          "product_id" => 1
          "article_number" => null
          "created_at" => "2021-11-26T09:47:25.000000Z"
          "updated_at" => "2021-11-26T09:47:25.000000Z"
          "product_mapping_options" => array:3 [
            0 => array:5 [
              "id" => 4
              "product_mapping_id" => 4
              "product_option_id" => 1
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            1 => array:5 [
              "id" => 5
              "product_mapping_id" => 4
              "product_option_id" => 3
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            2 => array:5 [
              "id" => 6
              "product_mapping_id" => 4
              "product_option_id" => 5
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
          ]
        ]
        1 => array:6 [
          "id" => 5
          "product_id" => 1
          "article_number" => null
          "created_at" => "2021-11-26T09:47:25.000000Z"
          "updated_at" => "2021-11-26T09:47:25.000000Z"
          "product_mapping_options" => array:4 [
            0 => array:5 [
              "id" => 7
              "product_mapping_id" => 5
              "product_option_id" => 1
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            1 => array:5 [
              "id" => 8
              "product_mapping_id" => 5
              "product_option_id" => 3
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            2 => array:5 [
              "id" => 9
              "product_mapping_id" => 5
              "product_option_id" => 5
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            3 => array:5 [
              "id" => 10
              "product_mapping_id" => 5
              "product_option_id" => 7
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
          ]
        ]
        2 => array:6 [
          "id" => 6
          "product_id" => 1
          "article_number" => null
          "created_at" => "2021-11-26T09:47:25.000000Z"
          "updated_at" => "2021-11-26T09:47:25.000000Z"
          "product_mapping_options" => array:4 [
            0 => array:5 [
              "id" => 11
              "product_mapping_id" => 6
              "product_option_id" => 1
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            1 => array:5 [
              "id" => 12
              "product_mapping_id" => 6
              "product_option_id" => 3
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            2 => array:5 [
              "id" => 13
              "product_mapping_id" => 6
              "product_option_id" => 5
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            3 => array:5 [
              "id" => 14
              "product_mapping_id" => 6
              "product_option_id" => 8
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
          ]
        ]
        3 => array:6 [
          "id" => 7
          "product_id" => 1
          "article_number" => null
          "created_at" => "2021-11-26T09:47:25.000000Z"
          "updated_at" => "2021-11-26T09:47:25.000000Z"
          "product_mapping_options" => array:4 [
            0 => array:5 [
              "id" => 15
              "product_mapping_id" => 7
              "product_option_id" => 1
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            1 => array:5 [
              "id" => 16
              "product_mapping_id" => 7
              "product_option_id" => 3
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            2 => array:5 [
              "id" => 17
              "product_mapping_id" => 7
              "product_option_id" => 5
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
            3 => array:5 [
              "id" => 18
              "product_mapping_id" => 7
              "product_option_id" => 9
              "created_at" => "2021-11-26T09:47:25.000000Z"
              "updated_at" => "2021-11-26T09:47:25.000000Z"
            ]
          ]
        ]
      ]

Here I searched via product_mappings with the following search scope:

    public function scopeProductOption(Builder $query, ...$productOptionTypeIds): Builder
    {
        foreach($productOptionTypeIds as $productOptionTypeId) {
            $query->whereHas('ProductMappingOptions', function (Builder $query) use ($productOptionTypeId) {
                $query->where('product_option_id', $productOptionTypeId);
            });
        }

        return($query);
    }

In the example I specified the values 1,3 and 5 (which was then passed into $productOptionTypeIds).

What I would like now: I would like to get 100% exactly displayed which product_mappings match the search, Say I would like to find e.g. only one entry where exactly 1,3 and 5 is mapped and not also 1,3,5 and 7,9 or 11. So the entries which then have 4 relations should fall away, because I don’t want to have the entries where something is "over".

Do you have any idea how I can "catch" exactly this one entry? So in the example above I would only want entry 0 from data. If I specify 1,3,5 and 9, I would only want entry 3 from data.

Source: Laravel

Published
Categorised as eloquent, eloquent--relationship, laravel Tagged , ,

Answers

Leave a Reply

Still Have Questions?


Our dedicated development team is here for you!

We can help you find answers to your question for as low as 5$.

Contact Us
faq