Laravel与条件有很多很多关系

Question

I have 4 tables users, opportunities, level_translations and levels

users
id, name, email

opportunities
id, client_name, user_id, total_price

levels
id, min, max

level_translations
id, name, description, locale, level_id

I want to get level name in current locale for given user id .. i'm using laravel-translatable package to localize my application

to get level it should be something like this

select * from levels where sum(opportunities.total_price) < levels.max and sum(opportunities.total_price) > levels.min 

Answer 1

I found a solution. my solution contain tow parts
first part
creating a MYSQL Function, i call it sumUserOwnopportunities(USERID)

    DELIMITER $$
CREATE  FUNCTION `sumUserWonOpportunities`(USERID INT) 
 RETURNS double
BEGIN
     DECLARE TOTAL_SUM DOUBLE DEFAULT 0;
     SELECT SUM(opportunities.total_price) from opportunities 
     WHERE opportunities.user_id=USERID AND (opportunities.status=1 OR opportunities.status=2) INTO TOTAL_SUM;
        RETURN TOTAL_SUM;
END$$
DELIMITER ;

second part
in User model

function level(){
   return  Level::where(function ($q){
            $q->where("min","<",DB::raw(" sumUserWonOpportunities($this->id)"))
            ->where("max",">",DB::raw(" sumUserWonOpportunities($this->id)"));
        })->first();
}

now I can call

$user=User::find(1);
$level=$user->level()->name;// it will return level name in current locale 

I hope this useful.