Problem when joining the same table multiple times

[xavadu]

Hello,

Sometimes there are relationship like the following:

Tables:

user

id
username

countries

id
name

country_in

id
country_id
...

country_out

id
country_in

If the model relationship is like

user -> country_in -> countries
|-> country_out -> countries

we have this query generated

select count(*) as aggregate from `users` 
left join `country_in` on `country_in`.`id` = `users`.`country_in_id` 
left join `countries` on `countries`.`id` = `country_in`.`country_id` 
left join `country_out` on `country_out`.`id` = `users`.`country_out_id` 
left join `countries` on `countries`.`id` = `country_out`.`country_id` 

which throw "SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'countries'

I am thinking how to resolve it, and I have few ideas that I would like to discuss which one is the best or maybe can be another solution in order to resolve the problem:

1. Add a new property like $useTableUniqueAlias and in EloquentJoinBuilder.php:184 check for that flag and add a random salt to the sha1.

2. Other idea could be to check in all the relationship if the table is duplicated and also generate a unique sha1 for each one.

3. Directly use random hash instead of sha1 (I have to see better the code if it could have issues with other parts)

4. Any other solution?

Cheers

[fico7489]

maybe the best solution is to add counter :

categories
categories_2
categories_3
//...

$posts = Post::joinRelations('firstCategories')
	->join('secondCategories')
	->orderBy('firstCategories.id', 'desc');

[xavadu]

It would be like solution 2, adding a counter before the sha1 is generated in case of use hash, I will check in which part of the code would be the best place to do it, but if you already know, please guide me with a line number or function name :)

[xavadu]

If you say that we should have two tables in our app, I do not see it as a good practice, because it would be necessary to have duplicated information, since countries and countries_2 would be the same.

I think that better will be to resolve it in the package code.