XF 2.0 Converting a query to a finder

[AndyB]

Hello,

I'm in the process of converting my Most Likes v2.8 add-on located here:

https://xenforo.com/community/resources/most-likes.3352/

The query I would like to convert to a finder is this:

// run query
$users = $db->fetchAll("
	SELECT xf_user.user_id,
	xf_user.username,
	xf_user.avatar_date,
	COUNT(xf_post.likes) AS mostLikes
	FROM xf_post
	LEFT JOIN xf_user ON xf_user.user_id = xf_post.user_id
	LEFT JOIN xf_liked_content ON xf_liked_content.content_id = xf_post.post_id
	WHERE xf_post.likes > ?
	AND xf_liked_content.like_date >= ?
	AND xf_user.username IS NOT NULL
	GROUP BY xf_post.user_id
	ORDER BY mostLikes DESC, xf_user.username ASC
	LIMIT ?
", array(0, $timestamp, $limit));

The documentation is here, but I'm not having any luck:

https://xf2demo.xenforo.com/dev-docs/entities-finders-repositories/#the-finder

The following finder works well:

$userFinder = $this->finder('XF:User')
	->isValidUser()
	->order('register_date', 'DESC')
	->limit($this->options['limit']);

$viewParams = [
	'users' => $userFinder->fetch()
];

but it only finds the newest users. I'm trying to get users with the most likes.

Thank you.

 

[AndyB]

I'm making some progress with this code:

// run query
$results = $db->fetchAll("
    SELECT xf_user.user_id,
    COUNT(xf_post.likes) AS mostLikes
    FROM xf_post
    LEFT JOIN xf_user ON xf_user.user_id = xf_post.user_id
    LEFT JOIN xf_liked_content ON xf_liked_content.content_id = xf_post.post_id
    WHERE xf_post.likes > ?
    AND xf_liked_content.like_date >= ?
    AND xf_user.username IS NOT NULL
    GROUP BY xf_post.user_id
    ORDER BY mostLikes DESC, xf_user.username ASC
    LIMIT ?
", array(0, $timestamp, $limit));

foreach ($results AS $k => $v)
{
    $userId = $v['user_id'];
    $conditions[] = ['user_id', '=', $userId];
}

$finder = \XF::finder('XF:User');
$users = $finder->whereOr($conditions)->fetch();

 

[AndyB]

Now I need to figure out how to add to an object.

Is it possible to do a foreach and add to the $users object?

I'm trying this, but it's not working correctly.

foreach ($results AS $k => $v)
{
    $users->mostLikes = $v['mostLikes'];
}

 

[Snog]

So far as a finder...

Hints:
$finder->expression(...)
$finder->whereSql(...)

If you aren't using an IDE, it might be well worth looking into it. The available options will be displayed as you type. This is from PHP Storm, but Eclipse does it and I would imagine others do too...

 

[Kirby]

I am somewhat inclined to say that this is a use case beyond the capabilities of finders:
You can't do a GROUP BY with them.

Even if it was possible, the aggregated value doesn't belong to any entity so it can't be stored anywhere.

Would be interesting to get some thoughs from @Mike on this

 

[AndyB]

I ended up going in another direction with this add-on. I added a new field to the xf_user table and will update the field using a Cron entry once per hour.

 

[Mike]

Would be interesting to get some thoughs from @Mike on this

Roughly speaking, assuming you want to use entities, I'd recommend doing a minimal query to get the primary key for whatever entity you want and any "sidecar" data you want to include with it. Then go back an load the entities based on those keys and combine that data into an array where the entity and the extra data are alongside each other (rather than the data being "within" the entity).

This isn't a super common pattern, but you can see it when working with outdated templates.

There were some thoughts of allowing extra data with finders but it's not something that has been implemented (and I don't know if it will), so this is basically the main approach that exists now.

 

[AndyB]

Then go back an load the entities based on those keys and combine that data into an array

Doing this would not allow passing an object to the viewParams to be used in a template foreach loop, some rely on an object, for example the $users object used in many templates.

There were some thoughts of allowing extra data with finders but it's not something that has been implemented (and I don't know if it will)

Hopefully it will be implemented so we don't have to add fields to existing tables.

 

[Mike]

You can pass whatever data you want to a template, so you definitely would be able to pass an object to the template within an array. The outdated template list uses the approach I discussed above almost exactly. You can also see how that is used within a template then too.

 

[AndyB]

You can pass whatever data you want to a template

Sorry for my poor choice of words, allow me to be more clear. What I meant was there's no point passing an array to the template because some foreach loops require an object, as it does in this template:



So your suggestion to "combine that data into an array" would not work in this case. Is there some way around this issue?

 

[Lukas W.]

The reason that template is able to loop over the entity set (which is an object) is, that it Implements array access. You wouldn't be able to iterate over it otherwise. Arrays are for iteration, so why shouldn't you be able to iterate over them.

 

[AndyB]

The reason that template is able to loop over the entity set (which is an object) is, that it Implements array access. You wouldn't be able to iterate over it otherwise. Arrays are for iteration, so why shouldn't you be able to iterate over them.

If I replace the $users variable with an array, the avatar no longer show an image and when I hover over the avatar the tool tip does not pop up. So I did a dump on $users saw it's an object. So I concluded that in order to use the foreach with this template code I need an object and an array will not suffice, is this conclusion correct?

 

[Lukas W.]

Let's do a little crash course in iteration:

When you use the finder system to get a set of entities, the last function you normally call is fetch()

Looking at \XF\Mvc\Entity\Finder, you'll se the following in the return line of that function:

return $this->em->getBasicCollection($output);

em is an instance of \XF\Mvc\Entity\Manager, so we can look there for what is actually returned:

public function getBasicCollection(array $entities)
{
        return new ArrayCollection($entities);
}

\XF\Mvc\Entity\ArrayCollection doesn't hold any code itself that helps in understanding what's going on, but it extends \XF\Mvc\Entity\AbstractCollection, which itselfs implements \Countable, \IteratorAggregate and \ArrayAccess.

The ArrayCollection itself is an Object, but ArrayAccess enables php to treat it, as if it would be an Array. That means, you can access the elements that the ArrayCollection holds by keys, as if it was an array and most importantly, you can iterate over it. <xf:foreach> is the template equivalent of phps foreach(). Thanks to ArrayAccess, php can treat your object like an array and iterate over the elements it holds. So you can replace the object without an array and you will still be able to iterate over it.

So why is your code not working? The answer lies in your code above. If you'd throw an array into it, that does nothing but hold user objects (which is fairly pointless at this point, because it would serve the same purpose as the ArrayCollection), you'd notice that the avatars would be functioning. So the mystery lies in the <xf:avatar> function. To shorten it, you'll need to pass a user entity into it for it to work. What @Mike was pointing out earlier is, that you need to store the user entity alongside all additional information you need in your template.

For example your array could look like this:

array(
    // First User
    array(
        'user' => Object, // This is your user entity
        'like_count' => Integer, // The like count integer
        [...]
    ),
    // Second user
    array(
        [...]
    ),
    // ...
    [...]
)

When iterating over that array in the template, you'll then need to throw only the user entity into the <xf:avatar>-function.

<xf:foreach loop="$users" value="$array">
    <xf:avatar user="$array.user" size="s" img="true" />
</xf:foreach>

 

[AndyB]

Hi katsulynx,

I greatly appreciate you teaching me how to do this.

The following code works perfectly.

// run query
$results = $db->fetchAll("
SELECT content_user_id,
COUNT(*) AS mostLikesCount
FROM xf_liked_content
WHERE like_date >= ?
AND content_type = ?
GROUP BY content_user_id
ORDER BY mostLikesCount DESC
LIMIT ?   
", array($timestamp, 'post', $limit));

// define variable
$viewParams = array();

// continue if not empty
if (!empty($results))
{
    // get conditions
    foreach ($results AS $k => $v)
    {
        $userId = $v['content_user_id'];
        $conditions[] = ['user_id', '=', $userId];
    }

    // create users object
    $finder = \XF::finder('XF:User');
    $users = $finder->whereOr($conditions)->fetch();

    // create mostLikes array
    foreach ($results AS $k => $v)
    {
        $mostLikes[$v['content_user_id']] = array(
            'user' => $users[$v['content_user_id']],
            'most_likes_count' => $v['mostLikesCount']
        );
    }

    // prepare viewParams
    $viewParams = [
        'mostLikes' => $mostLikes
    ];
}

// send to widget
return $this->renderer('andy_mostlikes', $viewParams);

The template code:

<xf:if is="{{ $xf.visitor.hasPermission('mostLikes', 'view') }}">

<xf:css src="andy_mostlikes.less" />

<xf:if is="$mostLikes is not empty">
    <div class="block"{{ widget_data($widget) }}>
        <div class="block-container">
            <h3 class="block-minorHeader"><a href="{{ link('members/?key=most_likes') }}">{{ phrase('mostlikes_most_likes_past_x_days') }}</a></h3>
            <div class="block-body block-row">
                <ul class="listHeap">
                    <xf:foreach loop="$mostLikes" value="$mostLike">
                        <li>
                            <xf:avatar user="$mostLike.user" size="s" img="true" />
                            <div class="most-likes-number">{$mostLike.most_likes_count}</div>
                        </li>
                    </xf:foreach>
                </ul>
            </div>
        </div>
    </div>
</xf:if>   

</xf:if>

 

[Snog]

@AndyB , personally I think your other approach using a cron task was better.

Reason being is if that query is run on every page load it is adding an unneeded query to each page load. If everyone did that, we'd have pages with hundreds of queries taking place on each load. And since that appears to be a statistics display, it would be better to use a cron and quite possibly the simple cache to hold the values. Reserve queries for places where they are absolutely needed.

 

[Snog]

Hi Snog,

You make a very good point and I will leave the add-on as a cron task.

Question, you mentioned "simple cache to hold the values", would you be so kind to explain how this would be done.

Thank you.

Create simple cache:

$simpleCache = $this->app->simpleCache();
$mostLikesSerial = serialize($mostLikes);
$simpleCache['Your/Addon']['mostLikes'] = $mostLikesSerial;

Retrieve simple cache:

$simpleCache = $this->app->simpleCache();
$mostLikes = unserialize($simpleCache['Your/Addon']['mostLikes']);

Also, don't go hog wild with the amount of data stored in the simple cache. That is a public cache that can be used by all add-ons and does have a limit to the amount it can contain (it's a huge limit, but none the less a limit).