Laravel select with condition for join

Question

I have 3 tables: Outputs, Products and Services in my Laravel 5 project.

I want to JOIN these 3 tables in one QUERY based for this condition:

if outputs.article_type = 'p' (p = product) get the products.name

if outputs.article_type = 's' (s = service) get the service.name

$outputs = Output::join('products', 'products.id', '=', 'outputs.article_id')
                   ... only if outputs.article_type is p (p=product)
                ->join('services', 'services.id', '=', 'outputs.article_id') 
                  ... only if outputs.article_type is s (s=service)
                ->select(array('outputs.output_at','---services or product---.name as article_name'))

Answer 1

Not sure why you need to put the logic in one query. Seems like a perfect example for applying KISS.

Why not go for a function with 1 param and plain old IF to increase readability and make your (and maybe other devs) life easier? I don't see any advantage in what you're trying to do. Apart from brevity, which, in this case, is not the most important factor, I suppose.

In Output.php model:

    /**
     * Retrieves output of the given type.
     *
     * @param  string $type Type of the output
     *
     * @return mixed Object or false in case of problems
     */
    public static function getOutput($type) {

        if (!isset($type) || empty($type) || !in_array($type, ['p', 's'])) {
            // Wrong type
            return false;
        }

        if ($type === 'p') {
            $baseQuery = Output::join('products', 'products.id', '=', 'outputs.article_id');
            $table = 'products';
        } else {
            $baseQuery = Output::join('services', 'services.id', '=', 'outputs.article_id');
            $table = 'services';
        }

        return $baseQuery
                   ->select('outputs.output_at', $table.'.name as article_name')
                   ->get();
    }

Then, in a controller

$output = Output::getOutput('p');

if ($output && count($output)) {
    // Do other stuff or pass the data to a view...
}

Keep in mind that this is not tested and might need slightly different implementation than I showed, however the idea remains clear.

Answer 2

check this out A join with additional condition using Query Builder or ELoquent

but maybe you should consider eager loading instead of joining?

Output::with('Product', 'Service')->....

of course you need to add the relationships first

public function service() {
   if ($this->article_type == 's')
       return $this->belongsTo('Service', 'article_id', 'id')
   else
       return null;
 }

and overriding the name attribute to get the specific name you need

public functon getNameAttribute() {
   if ($this->service)
       return $this->service->name;
   else if ($this->product)
       return $this->product->name;
}