SELECT DISTINCT for Editor

borconi

Hi.

I have been reading the forum and I saw that currently Select distinct isn't supported.... I had a look through the PHP files and I made the following modifications:

1) Editor.PHP
From:

public function pkey ( $_=null )
    {
        return $this->_getSet( $this->_pkey, $_ );
    }

To:

public function pkey ( $_=null )
    {
        return $this->_getSet( $this->_pkey, $_ );
    }

    public function distinct ($_=null)
    {
        return $this->_getSet( $this->_distinct, $_ );
    }

In the same file I have changed:
From:

private function _get( $id=null, $http=null )
    {
        
        $query = $this->_db
            ->query("select")
            ->table( $this->_table )
            ->get( $this->_pkey );

To:

    private function _get( $id=null, $http=null )
    {
        
        $query = $this->_db
            ->query("select")
            ->table( $this->_table )
            ->get( $this->_pkey );
        
        if ($this->distinct())
             $query->distinct(true);

Also to make the rowcount correct I have added the same distinct condition in the next places:

        // Get the number of rows in the result set
        $ssp_set_count = $this->_db
            ->query('select')
            ->table( $this->_table )
            ->get( 'COUNT('.$this->_pkey.') as cnt' );
        if ($this->distinct())
             $query->distinct(true);
        $this->_get_where( $ssp_set_count );
        $this->_ssp_filter( $ssp_set_count, $http );
        $this->_perform_left_join( $ssp_set_count );
        $ssp_set_count = $ssp_set_count->exec()->fetch();

        // Get the number of rows in the full set
        $ssp_full_count = $this->_db
            ->query('select')
            ->table( $this->_table )
            ->get( 'COUNT('.$this->_pkey.') as cnt' );
        if ($this->distinct())
             $query->distinct(true);

Once all this was done I used my editor instance as follows:

$editor=Editor::inst( $db, 'jobs' )
    ->fields(
        Field::inst( 'company.Name' ),
        Field::inst( 'stores.short_add' )->validator( 'Validate::notEmpty' ),
                ......
                )
        ->LeftJoin(......);
$editor->distinct(true); //Only add this line if you want distinct values!
$editor->where(.....);
$editor->on(.....);

This looks to be working correctly however I haven't gone through all the classes and builds and such so by adding this modification I might have knocked out something different, but as a first it looks to me that it does what I expect it to do, namely adding the word DISTINCT in the query.

Allan, do you think I have messed up something bady or is there anything I should be aware by making this changes?

Thank you.

allan

Looks good - thanks for posting your changes!

I'm curious what the use case is. Editor doesn't provide SQL aggregation options, so why would distinct rows be useful?

Thanks,
Allan

borconi

Well it doesn't provide UNION's that's true but I have worked around it with WHERE IN.

In this case I have a table which contains all jobs, and who is the owner of that job and I have a second table where jobs can be subcontracted out due to a nasty logic of the business which doesn't make too much logical sense you might end up that on some jobs your are the owner but your also the subcontractor... I know this sound very very silly at first but it's quite a complex scheme behind it... and I need to create a particular view where you should see all your jobs + subcontracted jobs, so if I have the above case it will end up in duplicate rows, hence needing the DISTINCT.

Hope it makes sense...

allan

Very interesting - thanks! That does make a lot of sense and I will consider adding this in a future release.

As far as I can see, the code above looks good!

Regards,
Allan

borconi

Thanks Allan.

That will be nice, because that means I don't have to worry about this on future updates.

There is another case which can happen in real life for example where this will be practical.

Imagine Table A whit some data

And you have table X,Y and Z which contains information about different groups. You want to list all the data from table A where the primary key can be found in table X or Y, without actually linking them to table X and Y, just using them as filter, but some of the primary keys can be found in all 3 tables, X and Y and Z as well, so without a DISTINCT you will end up displaying duplicate rows for some of the elements.

Hope I was able to express myself in an understandable way...

allan

Yes that does make sense, although I think equally you could do that by selecting from table A and applying a condition if you want it to display only entires which are used in other tables. It is another useful case though - thanks!

Allan

bigern70

This feature was very helpful to me. If it could be included in a future release that would be great. Like everything else in software there are many different ways to get things done (especially in SQL). I happened to use the DISTINCT in a few instances and using this saved me time doing a redesign. (BTW - I have had editor for about 3 months now and I absolutely love it - great product!)