XF 2.2 Conditional delete from database with entity framework

[stromb0li]

How do I delete rows from a database using entity framework with a condition?

For example, I could use the following query today:

$db->query('DELETE FROM mytable WHERE timestamp > ?', [$timestamp]);

But I was trying to use the finder to scope the rows and delete, but doesn't seem like delete is an option?

\XF::finder('myaddon:myentity')->where('timestamp', '>', $timestamp)->delete();

Thank you!

 

[Jeremy P]

Entities don't support bulk operations like this directly as the entity life-cycle methods must run for each deleted entity. You can either implement a custom bulk deletion method in your repository which takes care of anything the life-cycle methods would for a single deletion, or fetch all the entities and iterate over them:

$db = \XF::db();
$db->beginTransaction();

$entities = $finder->fetch();
foreach ($entities AS $entity)
{
    $entity->delete(true, false);
}

$db->commit();

 

[asprin]

\XF::finder('myaddon:myentity')->where('timestamp', '>', $timestamp)->delete();

Try with this (not tested obviously)

$rows = \XF::finder('myaddon:myentity')->where('timestamp', '>', $timestamp);
foreach($rows as $singleRow){
    $singleRow->delete();
}

 

[stromb0li]

Sorry for newbie question here, but wouldn't this be much more compute intensive than just running the query? Iterating each row, if you have thousands, seems like it could become costly.

 

[Jeremy P]

Potentially, yeah. Most bulk entity operations in XenForo use the job queue to work in batches or implement custom repository methods to perform the necessary tasks while bypassing the ORM.

Nothing prevents you from using a traditional query, you're just responsible for taking care of any post-delete logic (cleaning up related entities, rebuilding caches, actions handled by behaviors, etc.):

\XF::db()->delete('some_table', 'timestamp > ?', [$timestamp]);

 

[stromb0li]

Most bulk entity operations in XenForo use the job framework to work in batches or implement custom repository methods to perform the necessary tasks while bypassing the ORM.

Do you have an example of this?

 

[Jeremy P]

Do you have an example of this?

They're largely down to what you want to achieve. You can do little more than sticking the delete query above into a repository method if that's all you want. If your entity has a post-delete hook which cleans up reactions, for example, you'd want to add some logic to handle that. It really depends on what other related data, caches, or systems you need to clean up simultaneously.

What's ORM?

The entity system is the ORM, which is a system for mapping database records to objects (similar to Hiberate, Eloquent, Doctrine, etc).

 

[stromb0li]

Thank you! This is super helpful!

 

[Jeremy P]

If you wanted to use the job queue to use the ORM to do deletions in batches, you could do something like:

<?php

namespace Some\AddOn\Job;

use XF\Job\AbstractRebuildJob;

class DeleteThings extends AbstractRebuildJob
{
    /**
     * @param int $start
     * @param int $batch
     *
     * @return list<int>
     */
    protected function getNextIds($start, $batch): array
    {
        $db = $this->app->db();

        return $db->fetchAllColumn(
            $db->limit(
                'SELECT some_id
                    FROM xf_some_table
                    WHERE some_id > ?
                    ORDER BY some_id',
                $batch
            ),
            $start
        );
    }

    /**
     * @param int $id
     */
    protected function rebuildById($id): void
    {
        $entity = $this->app->em()->find('Some\AddOn:Entity', $id);
        $entity->delete();
    }

    protected function getStatusType(): \XF\Phrase
    {
        return \XF::phrase('some_phrase');
    }
}

You can enqueue a job like this:

\XF::app()->jobManager()->enqueueUnique(
    'someAddOnDeleteThings',
    'Some\AddOn:DeleteThings',
    [],
    false
);