src/snac/server/database/SQL.php
/**
     * Select records from table source by foreign key
     *
     * Constrain sub query where fk_id, but group by id and return max(version) by id. Remember, our unique
     * key is always id,version. Joining the fk_id constrained subquery with the table on id and version gives
     * us all of the relevant id,version records, and nothing else.
     *
     * An old bug grouped the subquery in fk_id, and then joined on fk_id, which was wrong. It had the effect
     * of only returning record(s) for the overall max version, so the bug was only apparent when there were
     * at least 2 versions in group of records.
     *
     * @param integer $fkID A foreign key to record in another table.
     *
     * @param integer $version The constellation version. For edits this is max version of the
     * constellation. For published, this is the published constellation version.
     *
     * @return string[] A list of records (list of lists) with inner keys matching the database field names:
     * version, ic_id, id, text, note, uri, language_id.
     *
     */
Select records from table source by foreign key
Constrain sub query where fk_id, but group by id and return max(version) by id. Remember, our unique
key is always id,version. Joining the fk_id constrained subquery with the table on id and version gives
us all of the relevant id,version records, and nothing else.

An old bug grouped the subquery in fk_id, and then joined on fk_id, which was wrong. It had the effect
of only returning record(s) for the overall max version, so the bug was only apparent when there were
at least 2 versions in group of records.

@param integer $fkID A foreign key to record in another table.

@param integer $version The constellation version. For edits this is max version of the
constellation. For published, this is the published constellation version.

@return string[] A list of records (list of lists) with inner keys matching the database field names:
version, ic_id, id, text, note, uri, language_id.
Select records from table source by foreign key Constrain sub query where fk_id but group by id and return max ( version ) by id . Remember our unique key is always id version . Joining the fk_id constrained subquery with the table on id and version gives us all of the relevant id version records and nothing else . An old bug grouped the subquery in fk_id and then joined on fk_id which was wrong . It had the effect of only returning record ( s ) for the overall max version so the bug was only apparent when there were at least 2 versions in group of records . @param integer $fkID A foreign key to record in another table . @param integer $version The constellation version . For edits this is max version of the constellation . For published this is the published constellation version . @return string [] A list of records ( list of lists ) with inner keys matching the database field names : version ic_id id text note uri language_id .
public function selectSource($fkID, $version)
    {
        $qq = 'select_source';
        $this->sdb->prepare($qq,
                            'select aa.version, aa.ic_id, aa.id, aa.text, aa.citation, aa.note, aa.uri, aa.language_id, aa.display_name
                            from source as aa,
                            (select id,max(version) as version from source where fk_id=$1 and version<=$2 group by id) as bb
                            where not is_deleted and aa.id=bb.id and aa.version=bb.version');
        $result = $this->sdb->execute($qq, array($fkID, $version));
        $all = array();
        while($row = $this->sdb->fetchrow($result))
        {
            array_push($all, $row);
        }
        $this->sdb->deallocate($qq);
        return $all;
    }
public function selectSource ( $ fkID , $ version ) { $ qq = 'select_source' ; $ this -> sdb -> prepare ( $ qq , 'select aa.version, aa.ic_id, aa.id, aa.text, aa.citation, aa.note, aa.uri, aa.language_id, aa.display_name
                            from source as aa,
                            (select id,max(version) as version from source where fk_id=$1 and version<=$2 group by id) as bb
                            where not is_deleted and aa.id=bb.id and aa.version=bb.version' ) ; $ result = $ this -> sdb -> execute ( $ qq , array ( $ fkID , $ version ) ) ; $ all = array ( ) ; while ( $ row = $ this -> sdb -> fetchrow ( $ result ) ) { array_push ( $ all , $ row ) ; } $ this -> sdb -> deallocate ( $ qq ) ; return $ all ; }
Select records from table source by foreign key
 Constrain sub query where fk_id, but group by id and return max(version) by id.
Select records from table source by foreign key Constrain sub query where fk_id but group by id and return max ( version ) by id .