src/snac/server/database/SQL.php
/**
     * Select list of dates
     *
     * Select a date knowing a date id values. selectDate() relies on the date.id being in the original table,
     * thus $did is a foreign key of the record to which this date applies. selectDate() does not know or care
     * what the other record is.
     *
     * 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.
     *
     * (What "other" date select? This old comment is unclear.) The other date select function would be by
     * original.id=date.fk_id. Maybe we only need by date.fk_id.
     *
     * @param integer $did 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.
     *
     * @param string $fkTable Name of the related table.
     *
     * @return string[] A list of date_range fields/value as list keys matching the database field names.
     */
Select list of dates
Select a date knowing a date id values. selectDate() relies on the date.id being in the original table,
thus $did is a foreign key of the record to which this date applies. selectDate() does not know or care
what the other record is.

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.

(What "other" date select. This old comment is unclear.) The other date select function would be by
original.id=date.fk_id. Maybe we only need by date.fk_id.

@param integer $did 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.

@param string $fkTable Name of the related table.

@return string[] A list of date_range fields/value as list keys matching the database field names.
Select list of dates Select a date knowing a date id values . selectDate () relies on the date . id being in the original table thus $did is a foreign key of the record to which this date applies . selectDate () does not know or care what the other record is . 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 . ( What " other " date select . This old comment is unclear . ) The other date select function would be by original . id = date . fk_id . Maybe we only need by date . fk_id . @param integer $did 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 . @param string $fkTable Name of the related table . @return string [] A list of date_range fields / value as list keys matching the database field names .
public function selectDate($did, $version, $fkTable)
    {
        $qq = 'select_date';
        $query = 'select
        aa.id, aa.version, aa.ic_id, aa.is_range, aa.descriptive_note,
        aa.from_date, aa.from_bc, aa.from_not_before, aa.from_not_after, aa.from_original,
        aa.to_date, aa.to_bc, aa.to_not_before, aa.to_not_after, aa.to_original, aa.fk_table, aa.fk_id,
        aa.from_type,aa.to_type
        from date_range as aa,
        (select id,max(version) as version from date_range where fk_id=$1 and fk_table=$3 and version<=$2 group by id) as bb
        where not is_deleted and aa.id=bb.id and aa.version=bb.version';
        $this->sdb->prepare($qq, $query);
        $result = $this->sdb->execute($qq, array($did, $version, $fkTable));
        $all = array();
        while($row = $this->sdb->fetchrow($result))
        {
            array_push($all, $row);
        }
        $this->sdb->deallocate($qq);
        return $all;
    }
public function selectDate ( $ did , $ version , $ fkTable ) { $ qq = 'select_date' ; $ query = 'select
        aa.id, aa.version, aa.ic_id, aa.is_range, aa.descriptive_note,
        aa.from_date, aa.from_bc, aa.from_not_before, aa.from_not_after, aa.from_original,
        aa.to_date, aa.to_bc, aa.to_not_before, aa.to_not_after, aa.to_original, aa.fk_table, aa.fk_id,
        aa.from_type,aa.to_type
        from date_range as aa,
        (select id,max(version) as version from date_range where fk_id=$1 and fk_table=$3 and version<=$2 group by id) as bb
        where not is_deleted and aa.id=bb.id and aa.version=bb.version' ; $ this -> sdb -> prepare ( $ qq , $ query ) ; $ result = $ this -> sdb -> execute ( $ qq , array ( $ did , $ version , $ fkTable ) ) ; $ all = array ( ) ; while ( $ row = $ this -> sdb -> fetchrow ( $ result ) ) { array_push ( $ all , $ row ) ; } $ this -> sdb -> deallocate ( $ qq ) ; return $ all ; }
Select list of dates
 Select a date knowing a date id values.
Select list of dates Select a date knowing a date id values .