XF 2.1 How do I re-write this query to use the finder function?

[HJW]

Hello,

I can write the query I want in standard SQL fine:

SELECT * FROM xf_tagess_tag_watch, xf_tag_content, xf_thread where xf_tagess_tag_watch.user_id = @userid
and xf_tag_content.tag_id = xf_tagess_tag_watch.tag_id
and xf_tag_content.content_id = xf_thread.thread_id
and xf_thread.discussion_open = 1
and xf_thread.discussion_state = 'visible'
order by xf_thread.last_post_date DESC

although I'm struggling to work out how to rewrite it for XF2 to use the finder function, this is my current stab and I think I'm far off.

Could anyone give me any help? thanks

        $finder
            ->with('fullForum')
            ->where('discussion_state', 'visible')
            ->sqlJoin('xf_tag_content', 'synonym', ['xf_tag_content.content_id','xf_thread.thread_id'], false)
            ->sqlJoinConditions('synonym', [['xf_tag_content.content_id', '=', '$xf_thread.thread_id']])
            ->sqlJoin('xf_tagess_tag_watch', 'synonym2', ['xf_tag_content.tag_id','xf_tagess_tag_watch.tag_id'], false)
            ->sqlJoinConditions('synonym2', [['xf_tag_content.tag_id', '=', 'xf_tagess_tag_watch.tag_id']])
            ->where('xf_tagess_tag_watch.user_id', $userId)
            ->setDefaultOrder('last_post_date', 'DESC');

 

[TickTackk]

Anytime you see the column content_type and content_id in a entity, you will be required to do 2 queries when using finder. First for getting the content(s) and the other to get whatever you're looking for while making sure the content_type and content_id are set to match of content(s) you previously fetched.

In your case you first to watch the watched tags with data from xf_tag_content table and then fetch the threads from the content ids you just got.

 

[Kirby]

I think I'm far off.

Yes

I assume that xf_tagess_tag_watch is a custom table?
If so, you will need to create an Entity (YourAddOnId:TagWatch) for this table and have it define a relation to XF:TagContent.

You will also have to extend entity XF:TagContent to have a relation to XF:Thread.

Once this is done your finder will be smth. like

$finder = \XF::app()->finder('YourAddOnId:TagWatch')
    ->where('user_id', $userId)
    ->where('TaggedThread.discussion_state', 'visible')
    ->where('TaggedThread.open', 1)
    ->setDefaultOrder('TaggedThread.last_post_date', 'DESC');