convert a sql query to php laravel query

I'd start with the examples in the query Builder chapter. There are good examples of groupby, aggregates Etc.

https://laravel.com/docs/7.x/queries

how can i write left join in laravel query .

Look at the example queries in that chapter. Not rewriting your query but here is an example of what one looks like:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

But if you are new to this seriously work some of those examples in the documentation and watch some free videos in the from scratch series.

And eloquent also has all query Builder methods.

https://laravel.com/docs/7.x/queries#joins

Right in that section Taylor has examples of a left join. Please review the documentation.

i am convert this into laravel query but doesnt work.

ProductVarientsModel::SELECT('categories.id' ,'categories.name','categories.image','categories.status',count('product_varients.id') as product_varients_count),
            (SubCategoriesModel::select(count(*) as subcategories_count)
                ->where('categories.id', 'subcategories.category_id') ,
            (ProductModel::select (count(*)as products_count 
            ->where('categories.id', 'products.category_id') 
            ->leftJoin('products', 'product_varients.product_id', '=', 'products.id')
            ->leftJoin('categories', 'products.category_id', '=', 'categories.id')
            ->groupBy('categories.id')

You can use your original query in laravel, use the db facade, here is an example:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Hi @lavnishad93

You want to convert to a raw query or are you trying to learn what Laravel has to offer?

Because your query is not that complex and I believe you should be able to achieve it by creating the corresponding Models, defining their relationships and just using Eloquent to obtain the results.

If you follow those steps, then you could do something like:

Category::with(['products', 'subcategories', 'product_varients'])
        ->withCount([
              'products as products_count', 
              'subcategories as subcategories_count', 
              'product_varients as product_varients_count'
        ])
        ->groupBy('id')
        ->get()

You can read how to define Relationships between Eloquent Models here: https://laravel.com/docs/7.x/eloquent-relationships

As well as Eager Loading (the with() section) at: https://laravel.com/docs/7.x/eloquent-relationships#eager-loading

And finally to understand how to Count Related Models: https://laravel.com/docs/7.x/eloquent-relationships#counting-related-models

Hope this is what you are looking for!

Hey @mvnobrega, i done that before the main problem is here no relation with the product_varient mean while categories relation with subcategory, product but not with product_varient , only relation with the product table only.

i done that query as you said but getting error.

#message: "Call to undefined method App\Model\CategoriesModel::product_varients()"

In fact i try this way, but did not getting result as i want

CategoriesModel::select('id', 'image', 'name', 'status')->where('deleted_at', NULL)
                 ->withCount('subcategories')
                 ->withCount(array('products' => function ($q) {
                     $q->where('publish', '1')->where('status', '!=', '2');
            }))
	    ->withCount(array('products' => function ($q) {
                     $q->withCount('product_varients');
            }))
	    ->get();

@lavnishad93 I'm not sure if != is accepted for a database query.

I know that it should be <> on MySQL although if you don't get an error, maybe Eloquent converts the operator to the correct form.

Anyway, try changing your $q->where('publish', '1')->where('status', '!=', '2'); line to $q->where('publish', '1')->where('status', '<>', '2'); to see if you get expected results.

Finally, can't you just add the missing product_varient relationship so you have everything you need?