XF 2.2 How do I write this query in a finder?

grantus

Active member
I'm just starting to learn to develop in Xenforo. I've been using the finder system for my queries, but just simple ones.

I have my own table called "test". Is it possible to do something like this with the finder?

Code:
SELECT *,
    like_num / listen_num AS percent
    FROM test

I currently use this code no problem, but in a finder I don't know how I can do the select statement like that. From what I see in the XF docs, it just shows simple finder queries and everything is based on certain conditions, unless I missed something.

Or do I have to just write out queries like this (and more complex queries) with regular SQL?
 

Jeremy P

XenForo developer
Staff member
In general, the finder is for fetching entity objects (though you can get the raw data via fetchRaw, specific columns via fetchColumns, or the configured query via getQuery).

Depending on your use case you could instead add a getter to your entity which returns the percentage by dividing the column values, or just use a manual query as you suggested.
 

grantus

Active member
In general, the finder is for fetching entity objects (though you can get the raw data via fetchRaw, specific columns via fetchColumns, or the configured query via getQuery).

Depending on your use case you could instead add a getter to your entity which returns the percentage by dividing the column values, or just use a manual query as you suggested.
I thought about a getter but I'd like to try it with just a raw query first to make sure I have everything right.

But how would I do a regular query? What is the syntax?

For example, I have this:
Code:
public function actionTop30(ParameterBag $params)
    {
    // my query $testQuery

    SELECT *,
    like_num / listen_num AS percent
    FROM test
    WHERE listen_num >= 40
    AND active = 1
    GROUP BY test_id
    ORDER BY percent DESC LIMIT 30

        $page = $params->page;
        $perPage = 20;

        $testQuery->limitByPage($page, $perPage);

        $viewParams = [
            'test' => $testQuery->fetch(),
            'page' => $page,
            'perPage' => $perPage,
            'total' => $testQuery->total()
        ];

        return $this->view('Demo\ILL:Test', 'test_top30', $viewParams);
    }
 

grantus

Active member
Use the database adapter:
PHP:
$values = \XF::db()->fetchAll('SELECT *, like_num / listen_num AS percent FROM test');
Thanks.

I tried it this way but I don't see how I'm supposed to integrate that with the rest of the code. In other words, doing a straight query doesn't seem to just replace the finder code. I can't seem to pass anything on to the viewParams or the pagination, it keeps giving errors.
 

grantus

Active member
Can you share the code snippet you are trying? This might help.
What I have above but now as a query. I'm just wondering how a regular query can replace the finder code but then still pass the results to the pagination and viewparams, etc. Like this:

Code:
public function actionTop30(ParameterBag $params)
    {
    
    $testQuery = \XF::db()->fetchAll('SELECT *,
    like_num / listen_num AS percent
    FROM test
    WHERE listen_num >= 40
    AND active = 1
    GROUP BY test_id
    ORDER BY percent DESC LIMIT 30');

        $page = $params->page;
        $perPage = 20;

        $testQuery->limitByPage($page, $perPage);

        $viewParams = [
            'test' => $testQuery->fetch(),
            'page' => $page,
            'perPage' => $perPage,
            'total' => $testQuery->total()
        ];

        return $this->view('Demo\ILL:Test', 'test_top30', $viewParams);
    }
 

Jeremy P

XenForo developer
Staff member
The database adapter methods return their results immediately, there is no intermediate object (or fetch method to get the results). In your example, $testQuery will be a two dimensional array of database results and you would pass that directly into your view params.

The finder's limitByPage method is really just a shorthand for setting a LIMIT and OFFSET on the underlying query. When writing manual queries, you would have to do this on your own.

Still, it's usually more idiomatic to work with the finder and entity objects, and only use direct queries for special cases. For derived data (like percent), you would add a getter to your entity to compute the values from the columns. So I'd probably recommend going down that route instead.
 

grantus

Active member
Yes, I understand how the finder and entity setup is ideal, however with the documentation it's very vague when dealing with something complex.

How would I do the column division with a getter? I've been looking through some of the XF files but can't seem to find how I can do something this simple.

But also, what if I have a query with column aliases? I have plenty of queries from my own tables that need that but again from what I've seen in various XF files, I feel lost.
 

Jeremy P

XenForo developer
Staff member
How would I do the column division with a getter? I've been looking through some of the XF files but can't seem to find how I can do something this simple.
Define the getter in your entity structure:

PHP:
$structure->getters = [
    'percent' => true,
];

The key is the desired name of the property, and the value is a boolean indicating whether or not to cache the value on first retrieval (true) or recompute it every time it is accessed (false).

Add the corresponding method to your entity:

PHP:
public function getPercent(): float
{
    return $this->like_num / $this->listen_num;
}

You can then use $entity->percent as if it were any other column.

But also, what if I have a query with column aliases? I have plenty of queries from my own tables that need that but again from what I've seen in various XF files, I feel lost.
It really depends on your use case. Generally, you compute derived data in PHP using getters instead of SQL.
 

grantus

Active member
Define the getter in your entity structure:

PHP:
$structure->getters = [
    'percent' => true,
];

The key is the desired name of the property, and the value is a boolean indicating whether or not to cache the value on first retrieval (true) or recompute it every time it is accessed (false).

Add the corresponding method to your entity:

PHP:
public function getPercent(): float
{
    return $this->like_num / $this->listen_num;
}

You can then use $entity->percent as if it were any other column.


It really depends on your use case. Generally, you compute derived data in PHP using getters instead of SQL.
Ah, so getters is just to define what I need? Then I set up whatever methods I need?

When I have something like this:

Code:
SELECT * FROM table1
    JOIN
    (SELECT user_id AS sub_user_id, SUM(points) AS total_points, COUNT(*) AS entered FROM table2 WHERE user_id != '0' GROUP BY user_id) AS subquery

That's where I can see myself giving up! I'm just trying to wrap my head around the XF framework and how I can integrate it with some existing code from custom tables. I know I can set up relations inside the Entity, so I'm assuming I can have multiple table relations as well?
 

Jeremy P

XenForo developer
Staff member
Ah, so getters is just to define what I need? Then I set up whatever methods I need?
Yes. You just define what getters are available in the structure, and then those properties will pull their values from the corresponding method.

That's where I can see myself giving up! I'm just trying to wrap my head around the XF framework and how I can integrate it with some existing code from custom tables.
One of the biggest issues people have with ORMs (which the finder/entity system ultimately comprise) is the resulting impedance mismatch. ORMs are very convenient for simple cases, but they can get in your way when you need to stray from the happy path.

On the whole, an entity is an object which corresponds to an entire row in a table. The properties of the object correspond to the columns of the row. You can use getters to derive data from the columns (or elsewhere). A finder is for selecting one or more entire rows from a table and using them to hydrate entity objects. Doing anything more (or less) than that can be challenging to map cleanly to this paradigm.

I know I can set up relations inside the Entity, so I'm assuming I can have multiple table relations as well?
You can use relations to build a sort of graph of entity objects, but again that comes with all the limitations inherent to those objects, and can require thinking about your data and designing your schema a bit differently.

When I have something like this:
In this case, the usual approach would likely be to denormalize the data by storing total_points and entered as columns on the parent table (table1), and updating their values when data is changed on the child table (table2).

An alternative approach might be to have total_points and entered getters on your table1 entity. The corresponding methods would fetch the data for that entity only (via a manual query).

However, in the presence of multiple entities (rows) this results in one of the most infamous issues resulting from ORMs: n+1 queries (or for both getters, 2n+1 queries).

To work around this, a common pattern is to fetch the getter data for all of the entities in one query and use the results to hydrate the getters:

Create setters on your entity:
PHP:
public function setTotalPoints(int $totalPoints): void
{
    $this->_getterCache['total_points'] = $totalPoints;
}

// ...

And then where you're fetching the entities:
PHP:
// ...
$entities = $finder->fetch();

$db = \XF::db();
$userIds = $entities->pluckNamed('user_id');
$data = $db->fetchAllKeyed(
    'SELECT user_id, 
            SUM(points) AS total_points, 
            COUNT(*) AS entered 
        FROM table2 
        WHERE user_id IN (' . $db->quote($userIds) . ')
        GROUP BY user_id',
    'user_id'
);

foreach ($data as $userId => $row) {
    $entity = $entities[$userId];
    $entity->setTotalPoints($row['total_points']);
    // ...
}

This will give you the data you need ($entity->total_points etc.) on all of the entities in two total queries. Normally you would abstract this into a repository method so you can call it where ever you may need it. Of course this is all a bit complex, so I'd just focus on getting it working and understanding how the pieces fit together first.
 

grantus

Active member
One of the biggest issues people have with ORMs (which the finder/entity system ultimately comprise) is the resulting impedance mismatch. ORMs are very convenient for simple cases, but they can get in your way when you need to stray from the happy path.
Exactly. This is the issue I've been having because of course I'm trying to integrate my own table into XF's, which is not the same schema.

You can use relations to build a sort of graph of entity objects, but again that comes with all the limitations inherent to those objects, and can require thinking about your data and designing your schema a bit differently.
In this case, the usual approach would likely be to denormalize the data by storing total_points and entered as columns on the parent table (table1), and updating their values when data is changed on the child table (table2).
This is what I was thinking. I do have some tables where I supply the calculated totals into a column, rather than trying to calculate after querying the database. I think this would be my best approach since it makes the finder system much simpler for me wrap my head around.

Thank you for explaining a lot of this! It's getting interesting now since I see the possibilities.
 
Top