Eloquent ORM LeftJoin with ifnull and orOn using is not working

Question

I want to write this sql query with eloquent orm laravel 8.

Select * from apples as a

Left Join Brands as b 

On ( (IFNULL(a.x, '') || IFNULL(a.y, '') || IFNULL(a.z, '')) =
                    (IFNULL(b.t, '') || IFNULL(b.e, '') || IFNULL(b.f, '')))

My eloquent query:

  Apple::select('*')
   ->leftJoin('Brands', function ($join) {
                $join->on('Brands.t', '=', 'Apples.x');

                $join->on(function ($join2) {
                    $join2
                        ->orOn('Brands .t', '=', 'Apples.x')
                        ->whereRaw("REPLACE('Brands .t',' ','')!=?", '')
                        ->whereRaw("REPLACE('Apples.x',' ','')!=?", '')
                        ->where("Apples.x","!=",null)
                        ->where("Brands .t","!=",null);
                    $join2
                        ->orOn('Brands .e', '=', 'Apples.y')
                        ->whereRaw("REPLACE('Brands .e',' ','')!=?", '')
                        ->whereRaw("REPLACE('Apples.y',' ','')!=?", '')
                        ->where("Apples.y","!=",null)
                        ->where("Brands .e","!=",null);
                });

            })

Apple Columns

x -> Alphanumeric

y-> Alphanumeric

z-> Alphanumeric

Brand Columns

t-> Alphanumeric

e-> Alphanumeric

f-> Alphanumeric

All columns values can be null or empty string like this -> " " without.

Every time matching columns Apple x and Brand t.

For example db show. Any columns contain be whitespace values or null or empty string.

Apple

X	Y	Z
ABC	2
ABC		4
ABC
ABC	5	6

Brand

t	e	f	g
ABC	2	null	ABC_NL
ABC	''	4	ABC_TR
ABC	null	whitespace_string	ABC_USA
ABC	5	6	ABC_GER

How can do access true g result with laravel eloquent orm query ?

Answer 1

I solved this problem.

->leftJoin('Apple', function ($join) {
                $join->on(
                    DB::raw("IFNULL(Apple.x, '') || IFNULL(Apple.y, '') || IFNULL(Apple.z, '')"),
                    DB::raw("IFNULL(Brand.t, '') || IFNULL(Brand.e, '') || IFNULL(Brand.f, '')")
                );
            })