How to update values using the ORM instead of a raw SQL query?

Question

When a user wants to set an Image to be featured we want to reset all the other images to not be featured. I have this working using the following raw SQL query.

Is there a cleaner way to do this using the ORM or for this type of thing is it cleaner to keep it the way it is?

<?php

class Banner extends DataObject {
    private static $db = array(
        'FeaturedImage' => 'Boolean'
    );

    private static $has_one = array(
        'Image' => 'Image'
    );

    private static $summary_fields = array(
        'Image.CMSThumbnail' => 'Image',
        'FeaturedImage.Nice' => 'Featured?'
    );

    public function onBeforeWrite()
    {
        parent::onBeforeWrite();

        // When user sets an image to be featured, reset all other images not to be featured
        if ($this->FeaturedImage) {
            $query = 'UPDATE Banner SET FeaturedImage = 0 WHERE Banner.ID !=' . $this->ID;
            DB::query($query);
        }
    }
}

as greg has said, best in the onAfterWrite... but then again best to make some sort of CMSAction for this and not "hack" it into a save. — Barry
– Barry, Commented Sep 5, 2016 at 13:51
@ifusion cleaner, well... but one option is a has_one from the same object that holds the banners to one of the bannerobjects per dropdown or a listboxfiled. — munomono
– munomono, Commented Sep 5, 2016 at 15:44

Andy Adiwidjaja · Accepted Answer · 2016-09-09 07:47:08Z

1

Did you already try this (sorry, cannot add this as a comment)?

public function onAfterWrite()
{
    parent::onAfterWrite();

    if ($this->FeaturedImage) {
        $banners = Banner::get()->exclude('ID', $this->ID);
        foreach($banners as $banner) {
           $banner->FeaturedImage = false;
           $banner->write();
        }
    }
}

The condition should prevent the recursive call of write.

answered Sep 9, 2016 at 7:47

Andy Adiwidjaja

1314 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darjus Rackevic · Accepted Answer · 2016-09-05 10:50:20Z

1

something like this:

$banners = Banner::get()->exclude('ID', $this->ID);
foreach($banners as $banner) {
   $banner->FeaturedImage = 0;
   $banner->write();
}

answered Sep 5, 2016 at 10:50

Darjus Rackevic

293 bronze badges

4 Comments

ifusion Over a year ago

Nope, get an error: Fatal error: Maximum function nesting level of '256' reached, aborting! in Unknown on line 0

Greg Smirnov Over a year ago

did you try it inside onAfterWrite()?

Gavin Bruce Over a year ago

onBeforeWrite and onAfterWrite won't work here as they are run every time an Image object is processed. So when you have $banner->FeaturedImage = 0; $banner->write() this will then re-run onBeforeWrite and onAfterWrite for $banner. It gets stuck in an endless loop. AFAIK, the only way is a raw query which is what you are currently doing.

ifusion Over a year ago

Have just tried using this code in the onAfterWrite() instead. I had 3 banners, it would only work for the last one, if I tried to make the first or second banner a featured one it wouldn't change it.. Looks like the raw SQL may be the best way to go in the end..

Collectives™ on Stack Overflow

How to update values using the ORM instead of a raw SQL query?

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related