XF 2.2 How to use the finder when joining tables?

[Orit]

How should I use the XF finder with an sql query left joining 3 tables (I can join only 2 of them if it's simpler...)?

I'm working on adding API endpoints to an existing add-on (I've already succeeded in doing it for another add-on)
but have had a problem:

I have 3 tables. One has events, the 2nd has event categories (id and titles) and the 3rd links between the categories and the events.
I'm trying to combine them into a XF finder, to display the category titles beside each event.
How should I do it?
This is the query that gets the results I need:

SELECT eventt.event_id, eventt.event_date, eventt.event_title, eventt.event_description, category.category_id, category.category_name FROM
ewr_atendo_events AS eventt
LEFT JOIN
(SELECT c.event_id, categories.category_id, categories.category_name FROM
ewr_atendo_catlinks AS c
LEFT join
ewr_atendo_categories AS categories
ON
(c.category_id = categories.category_id)
) AS category
ON
(eventt.event_id = category.event_id);

Thanks!!

 

[Orit]

It was easier than I thought.

I used the event entity relations of CatLink and Category for fetching the category and category names,
and managed to retrieve the events, category id and category name quite simply with:
$finder = $this->finder('EWR\Atendo:Event')

Then, when calling the results:
$result->event_category=$this->CatLink->category_id;
$result->event_categoryName=$this->CatLink->Category->category_name;

I'm leaving it here as it may help others...

 

[Orit]

And this is the way to call columns from a relation:
$events = $eventFinder->with('CatLink', true)
->where('CatLink.category_id', $category)->fetch();