V
V
Vanya Huk2017-02-22 21:08:05
Laravel
Vanya Huk, 2017-02-22 21:08:05

How to write complex mysql query in laravel?

hello, i have a db query

select *, ( 

        SELECT COUNT(*) from `filter_products` where `product_id` in (
        
        select `category_products`.`product_id` from `category_products` where `category_products`.`category_id` = 1
        
        group by `category_products`.`product_id`
        
        )  and `filters` . `id` = `filter_products` . `filter_id`
        
        ) as total from `filters` where `id` in (
        
        select `filter_id` from `filter_products` where `product_id` in (
        
        select `category_products`.`product_id` from `category_products` where `category_products`.`category_id` = 1
        
        ) group by `filter_id`) 
        
        and `filter_group_id` = 2 order by `title` asc

how to write it with orm?
here is a part of the code, but I can’t put count into the orm
public function filters( $filters = null )
    {


    	return  Filter::/*distinct('total', function( $query ) use ($filters){

            $query  -> select('product_id')

                    -> from( 'filter_products' )

                    -> whereIn( 'product_id', function( $q ) use ($filters){

                        $q -> select('category_products.product_id')

                            -> from('category_products');

                        $q  -> where('category_products.category_id', $this -> category_id )

                            -> groupBy('category_products.product_id');

                    } ) -> where('filters.id', '=', 'filter_products.filter_id') -> count();



            })*/->whereIn('id', function( $query ) use ( $filters ) {

    		   		$query->select('filter_id')
              			  
              			  ->from('filter_products')
              		      
              		      ->whereIn('product_id', function( $q )  use ( $filters ) {

              		      					$q -> select('category_products.product_id')

              		      					   -> from('category_products');


              		      					// get ajax filters
              		      					if( $filters != null )
                                            {

                                                $q -> leftJoin('filter_products', 'category_products.product_id' , '=','filter_products.product_id' )

                                                    -> whereIn('filter_products.filter_id', $filters) ;

                                            }

              		      					 $q  -> where('category_products.category_id', $this -> category_id ) ;

              		      }) 

              		         -> groupBy('filter_id') ;

    	}) -> where('filter_group_id', $this -> filter_group_id ) -> orderBy('title', 'ASC') -> get() ;
    }

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question