Can't use column searches in same table relations

[affaron]

Hi!

Recently I discovered an issue when using multiple column search with different relation using the same table. Here's what I mean.

I have a model with relations:

public function country_from()
    {
        return $this->belongsTo(Country::class, 'from_country_id');
    }

    public function country_to()
    {
        return $this->belongsTo(Country::class, 'to_country_id');
    }

and two columns in the datatable with these relation (country_from.name, country_to.name). I need to use filtering by both columns at the same time and I got a problem: when the second relation is applied the join is omitted since it's already joined and same table used in WHERE condition:

select
  `borders`.*
from
  `borders`
  left join `countries` on `borders`.`from_country_id` = `countries`.`id`
where
  LOWER(`countries`.`name`) LIKE '%south africa%'
  and LOWER(`countries`.`name`) LIKE '%zimbabwe%'

This happens because of this code:

laravel-datatables/src/EloquentDataTable.php

Lines 251 to 253 in 9e96d1f

	if (! in_array($table, $joins)) {
	$this->getBaseQueryBuilder()->join($table, $foreign, '=', $other, $type);
	}

It makes since until you need to join the same table. I figured out a way to make it work like it supposed to in my case, but Im not sure it's PR worth hack and it's not thoroughly tested :) Here's what I did.
Generate an alias for the table if it's already joined and return it with performJoin method (sorry, not the cleanest code):

protected function performJoin($table, $foreign, $other, $type = 'left')
    {
        $alias = $table;
        $joins = [];

        foreach ((array) $this->getBaseQueryBuilder()->joins as $key => $join) {
            $joins[] = Str::before($join->table, ' as ');
        }

        if (in_array($table, $joins)) {
            $index = count(array_filter($joins, function ($n) use ($table) { return $n === $table; })) + 1;
            $alias = $table . '_' . $index;
            $other = str_replace($table, $alias, $other);
            $table = $table . ' as ' . $alias;
        }

        $this->getBaseQueryBuilder()->join($table, $foreign, '=', $other, $type);

        return $alias;
    }

and use this alias in the end of joinEagerLoadedColumn() method:

            $alias = $this->performJoin($table, $foreign, $other);
            $lastQuery = $model->getQuery();
        }

        return $alias.'.'.$relationColumn;

after that the lib can generate accurate query:

select
  `borders`.*
from
  `borders`
  left join `countries` on `borders`.`from_country_id` = `countries`.`id`
  left join `countries_2` on `borders`.`to_country_id` = `countries_2`.`id`
where
  LOWER(`countries`.`name`) LIKE '%south africa%'
  and LOWER(`countries_2`.`name`) LIKE '%zimbabwe%'

Maybe I'm missing something and there's a native or more simple way to do that without code changes? Please let me know, thanks

[yajra]

@affaron thank you for investigating. This is currently a package limitation and doesn't work yet with a self-join relationship. If you can add a support for it, please do not hesitate to submit a PR.

[DjVinnii]

Is there any update on this? It looks like I'm running into the same problem. To me it seems the addition of aliases should fix this problem.

[affaron]

Is there any update on this? It looks like I'm running into the same problem. To me it seems the addition of aliases should fix this problem.

I can share my final solution or make a PR a bit later (updated version of the code I mentioned in the original post)

[Seb33300]

Hello,
I just created a PR to fix that issue here: #3216
It's not totally finish, I still have an issue with order by to fix. But you should be able to confirm if it fixes your issue.