XF 2.0 Using finder to replace a query that has an inner join

[AndyB]

I have the following query which works fine:

$attendees = $db->fetchAll("
SELECT xf_andy_rsvp_register.user_id,
xf_andy_rsvp_register.thread_id,
xf_andy_rsvp_register.guest_count,
xf_andy_rsvp_register.note,
xf_user.username
FROM xf_andy_rsvp_register
INNER JOIN xf_user ON xf_user.user_id = xf_andy_rsvp_register.user_id
WHERE xf_andy_rsvp_register.thread_id = ?
ORDER BY xf_andy_rsvp_register.rsvp_register_id ASC
", $threadId);

I would like to use a finder to replace it.

My Entity

<?php

namespace Andy\Rsvp\Entity;

use XF\Mvc\Entity\Entity;
use XF\Mvc\Entity\Structure;

class RsvpRegister extends Entity
{
    public static function getStructure(Structure $structure)
    {
        $structure->table = 'xf_andy_rsvp_register';
        $structure->shortName = 'Andy\Rsvp:RsvpRegister';
        $structure->primaryKey = 'rsvp_register_id';
        $structure->columns = [
            'rsvp_register_id' => ['type' => self::UINT, 'autoIncrement' => true, 'nullable' => true],
            'user_id' => ['type' => self::UINT, 'required' => true],
            'thread_id' => ['type' => self::UINT, 'required' => true],
            'guest_count' => ['type' => self::STR, 'maxLength' => 10, 'default' => ''],
            'note' => ['type' => self::STR, 'default' => ''],
        ];
        $structure->getters = [];
        $structure->relations = [
            'User' => [
                'entity' => 'XF:User',
                'type' => self::TO_ONE,
                'conditions' => 'user_id',
                'primary' => true
            ]
        ];       

        return $structure;
    }   
}

In my controller I have the following PHP code:

$finder = \XF::finder('Andy\Rsvp:RsvpRegister');
$attendees = $finder
    ->where('thread_id', $threadId)
    ->with('XF:User')
    ->order('rsvp_register_id', 'ASC')->fetch();

I'm getting the following error:

LogicException: Unknown relation XF:User accessed on xf_andy_rsvp_register in src/XF/Mvc/Entity/Finder.php at line 630
[LIST=1]
[*]XF\Mvc\Entity\Finder->join() in src/XF/Mvc/Entity/Finder.php at line 572
[*]XF\Mvc\Entity\Finder->with() in src/addons/Andy/Rsvp/XF/Pub/Controller/Thread.php at line 109
[*]Andy\Rsvp\XF\Pub\Controller\Thread->actionIndex() in src/XF/Mvc/Dispatcher.php at line 249
[*]XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 88
[*]XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 41
[*]XF\Mvc\Dispatcher->run() in src/XF/App.php at line 1844
[*]XF\App->run() in src/XF.php at line 328
[*]XF::runApp() in index.php at line 13
[/LIST]

What am I doing wrong?

Thank you.

 

[Chris D]

Should be “User” not “XF:User”.

 

[AndyB]

Thank you, Chris. No more error message. What do I need to do to include the username in the $attendees object?

 

[Chris D]

The attendees is now a collection (like an array) of attendee entities.

If you are looping through that collection (in a template for example) then each attendee entity can now access the username:

<xf:foreach loop="$attendees" value="$attendee">
    {$attendee.User.username}
</xf:foreach>

 

[AndyB]

I added the following to my template:

<xf:foreach loop="$attendees" value="$attendee">
    {$attendee.User.username}
</xf:foreach>

but I get the following error when viewing a thread.

Template Compilation Error
public:andy_rsvp - Entity User (class: User) could not be found in /home/southbay/public_html/forums20b7/src/XF/Mvc/Entity/Manager.php:51

 

[Chris D]

Did you change this:

        $structure->relations = [
            'User' => [
                'entity' => 'XF:User',
                'type' => self::TO_ONE,
                'conditions' => 'user_id',
                'primary' => true
            ]
        ];

To:

        $structure->relations = [
            'User' => [
                'entity' => 'User',
                'type' => self::TO_ONE,
                'conditions' => 'user_id',
                'primary' => true
            ]
        ];

If so, that's wrong. The 'entity' class should still be XF:User.

 

[AndyB]

Now it's working perfectly. Thank you, Chris.

 

[AndyB]

In the same add-on I'm now trying to replace this query:

$db->query("
    INSERT INTO xf_andy_rsvp
        (thread_id, event_date, deadline, maximum)
    VALUES
        (?,?,?,?)
", array($threadId, $eventDate, $deadline, $maximum));

I tried this but it's not working:

$data = [
    'thread_id' => $threadId,
    'event_date' => $eventDate,
    'deadline' => $deadline,
    'maximum' => $maximum
];

$rsvp->fastUpdate($data);

I get the following error:

ErrorException: [E_NOTICE] Undefined variable: rsvp in src/addons/Andy/Rsvp/XF/Pub/Controller/Thread.php at line 234

[LIST=1]
[*]XF::handlePhpError() in src/addons/Andy/Rsvp/XF/Pub/Controller/Thread.php at line 234
[*]Andy\Rsvp\XF\Pub\Controller\Thread->actionRsvpCreateSave() in src/XF/Mvc/Dispatcher.php at line 249
[*]XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 88
[*]XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 41
[*]XF\Mvc\Dispatcher->run() in src/XF/App.php at line 1844
[*]XF\App->run() in src/XF.php at line 328
[*]XF::runApp() in index.php at line 13
[/LIST]

 

[arms]

Have you defined what $rsvp is above your code?

 

[AndyB]

Got it!

So the MySQL query:

$db->query("
    INSERT INTO xf_andy_rsvp
        (thread_id, event_date, deadline, maximum)
    VALUES
        (?,?,?,?)
", array($threadId, $eventDate, $deadline, $maximum));

is replaced with this:

$rsvp = $this->em()->create('Andy\Rsvp:Rsvp');
$rsvp->thread_id = $threadId;
$rsvp->event_date = $eventDate;
$rsvp->deadline = $deadline;
$rsvp->maximum = $maximum;
$rsvp->save();

 

[AndyB]

This delete query:

$db->query("
    DELETE FROM xf_andy_rsvp
    WHERE thread_id = ?
", $threadId);

is replaced with:

$rsvp = $this->em()->find('Andy\Rsvp:Rsvp', $threadId);
$rsvp->delete();

 

[AndyB]

This update query:

$db->query("
UPDATE xf_andy_rsvp SET
    event_date = ?,
    deadline = ?,
    maximum = ?
    WHERE thread_id = ?
", array($eventDate, $deadline, $maximum, $threadId));

is replaced with:

$rsvp = $this->em()->find('Andy\Rsvp:Rsvp', $threadId);

$data = [
    'event_date' => $eventDate,
    'deadline' => $deadline,
    'maximum' => $maximum
];

$rsvp->fastUpdate($data);

 

[AndyB]

Delete a row where you need to match two fields:

$finder = \XF::finder('Andy\Rsvp:RsvpRegister');
$attendee = $finder
    ->where('user_id', $userId)
    ->where('thread_id', $threadId)
    ->fetchOne();