princeoo7's avatar

Only return products where Eloquent dailyInventory has return records

I am using below code to get the records with filter and all.

Product::with(['daily_stocks' => function ($q) {
            $q->whereIssuedStock(480.000);
        }])->paginate();

now what this does it for paginate it selected 1 to 15 and on that it gets be daily_stocks record. and in the code case, very few have 480 issued stocks and for rest its empty array like below:

{
  "collection":
    {
      "current_page": 1,
      "data":
        [
          {
            "id": 1,
            "name": "Destin King",
            "description": "Nemo assumenda optio quia dolor quo laudantium. Aut sit dolor veritatis quos adipisci minima. Asperiores et ut maiores iure.",
            "statuses_id": 2,
            "created_at": "2022-11-10T09:03:01.000000Z",
            "updated_at": "2022-11-10T09:03:01.000000Z",
            "status": { "key": "suspended", "value": "Suspended" },
            "daily_stocks":
              [
                {
                  "id": 1,
                  "sku": "4c28fafa-10af-3873-bfeb-6212fcacd9df",
                  "opening_stock": "781.000",
                  "added_stock": "0.000",
                  "issued_stock": "480.000",
                  "closing_stock": "301.000",
                  "created_at": "2022-10-10T00:00:00.000000Z",
                  "updated_at": "2022-10-10T00:00:00.000000Z",
                },
                {
                  "id": 2,
                  "sku": "4c28fafa-10af-3873-bfeb-6212fcacd9df",
                  "opening_stock": "301.000",
                  "added_stock": "0.000",
                  "issued_stock": "480.000",
                  "closing_stock": "24.000",
                  "created_at": "2022-10-11T00:00:00.000000Z",
                  "updated_at": "2022-10-11T00:00:00.000000Z",
                },
              ],
          },
          {
            "id": 2,
            "name": "Keith Boyer",
            "description": "Id laudantium esse mollitia amet inventore. Ipsa ipsa nostrum voluptas. Veniam sit aspernatur doloremque ipsum qui. Molestias esse ut dignissimos ratione.",
            "statuses_id": 1,
            "created_at": "2022-11-10T09:03:01.000000Z",
            "updated_at": "2022-11-10T09:03:01.000000Z",
            "status": { "key": "active", "value": "Active" },
            "daily_stocks": [],
          },
          {
            "id": 3,
            "name": "Aurelio Lesch",
            "description": "Velit atque in quibusdam qui quasi. Quia occaecati similique est sit quia ab neque. Odit aut consequatur eligendi dignissimos minima inventore qui aspernatur. Quia qui est qui totam aut quos.",
            "statuses_id": 4,
            "created_at": "2022-11-10T09:03:01.000000Z",
            "updated_at": "2022-11-10T09:03:01.000000Z",
            "status": { "key": "on_hold", "value": "On hold" },
            "daily_stocks": [],
          },
          {
            "id": 4,
            "name": "Reid Hahn",
            "description": "Eveniet aliquid alias commodi consequatur et libero. Quasi ea ut est qui est. Et sit voluptatem et mollitia qui eaque. Quaerat repellendus enim tempore.",
            "statuses_id": 4,
            "created_at": "2022-11-10T09:03:01.000000Z",
            "updated_at": "2022-11-10T09:03:01.000000Z",
            "status": { "key": "on_hold", "value": "On hold" },
            "daily_stocks": [],
          },
          {
            "id": 5,
            "name": "Jailyn Hill Jr.",
            "description": "Quis illo et commodi quae odit. Eos minima illum sunt repellat dolorum. Omnis dolor praesentium nihil earum recusandae. Exercitationem vitae soluta nemo.",
            "statuses_id": 1,
            "created_at": "2022-11-10T09:03:01.000000Z",
            "updated_at": "2022-11-10T09:03:01.000000Z",
            "status": { "key": "active", "value": "Active" },
            "daily_stocks": [],
          },
        ],
    },
}

...

I don't want records with empty daily_stocks variable. but also want to get all the records with 480 value in pagination format.

0 likes
12 replies
tykus's avatar

Use has builder method if you're wanting to exclude only the Products that have no daily_stocks

Product::query()
    ->has('daily_stocks')
    ->with(['daily_stocks' => function ($q) {
        $q->whereIssuedStock(480.000);
    }])
    ->paginate();

Or whereHas if the whereStockIssued constraint also applies:

Product::query()
    ->whereHas('daily_stocks', fn ($q) => $q->whereIssuedStock(480.000))
    ->with(['daily_stocks' => fn ($q) => $q->whereIssuedStock(480.000)])
    ->paginate();
princeoo7's avatar

@tykus I have below custom code but and it have below code with whereHas and its not working :(

public function apply($query, $data)
    {
        if (isset($data['f'])) {
            foreach ($data['f'] as $filter) {
                $filter['match'] = isset($filter['filter_match']) ? $$filter['filter_match'] : 'and';
                $this->makeFilter($query, $filter);
            }
        }

        return $query;
    }

    protected function makeFilter($query, $filter)
    {
        // find the type of column

        if (strpos($filter['column'], '.') !== false) {
            // nested column

            list($relation, $filter['column']) = explode('.', $filter['column']);
            $filter['match'] = 'and';

            if ($filter['column'] == 'count') {
                $this->{camel_case($filter['operator'])}($filter, $query, $relation);
            } else {
                $query->whereHas($relation, function ($q) use ($filter) {
                    $this->{camel_case($filter['operator'])}($filter, $q);
                });
            }
        } else {

            // normal column
            $this->{camel_case($filter['operator'])}($filter, $query);
        }
    }

Gives error:

"SQLSTATE[42P01]: Undefined table: 7 ERROR:  missing FROM-clause entry for table \"products\"\nLINE 1: ...iants\".\"id\" = \"stocks\".\"product_variant_id\" where \"products\"...\n                                                             ^ (SQL: select count(*) as aggregate from \"stocks\" inner join \"product_variants\" on \"product_variants\".\"id\" = \"stocks\".\"product_variant_id\" where \"products\".\"id\" = \"product_variants\".\"product_id\" and \"closing_stock\" not between 0 and 10)"

when trying to do:

Product::withWhereHas('daily_stocks', function ($q) use ($request) {
            $q->advanceFilter();
        })->paginate();
princeoo7's avatar

@tykus f is just an array identifier. example as below:

/api/v1/products?page=1&limit=50&order_columns=id&order_direction=asc&f[0][column]=stocks.created_at&f[0][operator]=between&f[0][query_1]=2022-11-06&f[0][query_2]=2022-11-01

if you want to add more conditions then we go f[1][column]=xyz&f[1][operator]=asd&f[1][query_1]=xxx

tykus's avatar

@princeoo7 okay, so how do you use the result of apply?

Trying to understand why the SQL in the error message looks like it has escaped all of the double quotes?

princeoo7's avatar

@tykus the string is split into multiple variables based on period "." in the variable passed as parameter. now this works flawlessly for level 1 query. where relation between child and parent exist. but if I want to go for relation ship between child and grandparent i.e level 2 relation. This breaks....

Sinnbeck's avatar

Try using this to limit both the main query and the with the same way

Product::withWhereHas('daily_stocks', function ($q) {
            $q->whereIssuedStock(480.000);
        })->paginate();
1 like
princeoo7's avatar

@Sinnbeck I figured this one out from LaravelDaily :) still I have some other issue where I am not able to get data when using advanceFilter I have created. but custom query works :/

princeoo7's avatar

@tykus its the custom trail created to apply filters and then above code of makeFilters is part of it.

princeoo7's avatar

@sinnbeck and @tykus can we connect some how on discord, googlemeet or something else? I can share the project as its a dummy project by I think I am not able to put my query regarding how I can achieve certain output. I don't think it would take more than 15 mins or so to connect an get the end result. Please let me know if its possible. My timing is IST so for availability let me know you timing and I will be available on that time if we can do this online :)

jaseofspades88's avatar

I do admire the resolve of both @sinnbeck and @tykus. The amount of work and effort they each put in to help people for free, is incredible

Please or to participate in this conversation.