Using join in filters

[kprovorov]

First thank you for a great lib! I hope i will use it in my app.

I have a problem using join in filter.

I have 3 models in next hierarchy:

• Project
  • Task (related to Project model by project_id column)
    • TimeEntry (related to Task model by task_id column)

in my TimeEntryController i have next code:

public function index(Request $request)
    {
      $queryBuilder = new TimeEntryQueryBuilder(new TimeEntry, $request);
      return $queryBuilder->build()->paginate();
    }

in my TimeEntryQueryBuilder a have next:

public function filterByProject($query, $id)
    {
      return $query->join('tasks', 'time_entries.task_id', '=', 'tasks.id')->where('project_id', $id);
    }

so when a make request for /timeentries/project=2 i want to get all TimeEntries, that assigned to tasks, that assigned to project with id=2, but i have the following error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in order clause is ambiguous (SQL: select * from `time_entries` inner join `tasks` on `time_entries`.`task_id` = `tasks`.`id` where `project_id` = 2 order by `id` desc limit 100 offset 0)

Maybe you can help me, pls? )

[kprovorov]

I find the problem. I remove orderBy from config and it works. Looks like some conflict about id column when i use join.

[selahattinunlu]

@kprovorov

Firstly, I'm glad that your issue fixed :)

And yes, error may occurs when you use "joins" in this case. I think, it's normal. You can use "order_by" parameter also to fix it.

https://github.com/selahattinunlu/laravel-api-query-builder/wiki/Other-Examples#order_by I guess, you can use like this: ?order_by=time_entries.id,asc