XF 2.2 Column aliases with Finder

[Parsnip]

Is there a way to assign an alias to a column when using Finder, similar to SELECT Thread.Forum.Node.title AS forum_title ... ?

When selecting from XFost I'd like to get both the thread title and the node/forum title, but I only end up with the last column listed, which gets simply assigned to 'title'. If I swap the order around, I get the other column instead.

->fetchColumns(['Thread.Forum.Node.title', 'Thread.title']);

I am trying to avoid doing a second query to retrieve the node details.

 

[Lukas W.]

Fetch the relations through the with() method to query them the same tim you run your threads query, then access them through their relation properties.

$thread = $myFinder->with('Forum.Node')->fetchOne();
$threadTitle = $thread->title;
$nodeTitle = $thread->Forum->Node->title;

 

[Parsnip]

$thread = $myFinder->with('Forum.Node')->fetchOne();

Thanks, that worked however even if I remove with('Forum.Node') it still works ok. So is that strictly necessary?

Also, it returns results for threads in private nodes where the user does not have any permissions. Would you know how to add a permission check too please? I saw the below suggested somewhere, but it has no effect and all private node results are still returned.

$visitor = \XF::visitor();
$myFinder = \XF::finder('XF:Thread')->with('Forum', 'Forum.Node.Permissions|' . $visitor->permission_combination_id);

 

[Lukas W.]

Thanks, that worked however even if I remove with('Forum.Node') it still works ok. So is that strictly necessary?

The with method will make sure that those relations are queried during the initial query. If you leave it away, XenForo will dynamically load in relations as required, causing extra queries. If you only have a single thread, this will result in two extra queries, one for $thread->Forum and one for $thread->Forum->Node. If you have multiple threads, this will scale linearly. With the with() function, your query count will be exactly one query regardless of how many thready you have.

Also, it returns results for threads in private nodes where the user does not have any permissions. Would you know how to add a permission check too please? I saw the below suggested somewhere, but it has no effect and all private node results are still returned.

You'll need to filter the query down to a list of accessible nodes first:

    $nodeRepo = \XF::repository('XF:Node');
    $nodeList = $nodeRepo->filterViewable($nodeRepo->getNodeList());
    $viewableNodeIds = $nodeList->pluck('node_id');
    $threadFinder->where('node_id', '=', $viewableNodeIds);

You may also need to filter the discussion_state property on the thread. If you only want to deal with approved, undeleted threads, checking for it to be visible will be sufficient.

 

[Parsnip]

You'll need to filter the query down to a list of accessible nodes first:

    $nodeRepo = \XF::repository('XF:Node');
    $nodeList = $nodeRepo->filterViewable($nodeRepo->getNodeList());
    $viewableNodeIds = $nodeList->pluck('node_id');
    $threadFinder->where('node_id', '=', $viewableNodeIds);

Thanks for your help with this. I also have some forums that are not private but have View thread content = No for some usergroups, so they can browse them and see the thread titles but not the content unless they upgrade.

I'd like to exclude these nodes from the node list altogether, would you know any way of doing that?

Otherwise I could always use $thread.canPreview(), and I see that using $finder->fetch()->filterViewable() totally filters those nodes' threads out of the results.