Query Contributors table for GetEbooksByAuthor()

[colagrosso]

Edit: Let's go with this approach after the discussion in #377.

[acabal]

Looks fine to me, how would you prefer to do it?

I have a helper function in a separate project for creating set SQL, it's a case that comes up not infrequently. You could add it as a static function of Db, I imagine we're going to need it again soon:

public static function CreateSetSql(array $arr): string{
    $sql = '(';

    for($i = 0; $i < sizeof($arr); $i++){
        $sql .= '?,';
    }

    return rtrim($sql, ',') . ')';
}

[colagrosso]

Thanks for the helper function, I added it. The multiple author query is hard to read, but it's not common.

In both cases, the explain plan shows MySQL using the UrlName index (index2), and it seems performant.

Single author example:

EXPLAIN SELECT e.*                                            
                                        from Ebooks e                       
                                        inner join Contributors con using (EbookId)
                                        where con.MarcRole = "aut"           
                                            and con.UrlName = 'virginia-woolf'        
                                        order by e.EbookCreated desc;
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+---------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra                                                               |
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | con   | ref    | index1,index2 | index2  | 1022    | const          | 6    | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | e     | eq_ref | PRIMARY       | PRIMARY | 4       | se.con.EbookId | 1    |                                                                     |
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+---------------------------------------------------------------------+

Multiple author example:

EXPLAIN SELECT e.*
                                        from Ebooks e
                                        inner join Contributors con using (EbookId)
                                        where con.MarcRole = "aut"
                                            and con.UrlName in ('william-wordsworth','samuel-taylor-coleridge')
                                        group by e.EbookId
                                        having count(distinct con.UrlName) = 2
                                        order by e.EbookCreated desc
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+---------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra                                                               |
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+---------------------------------------------------------------------+
|    1 | SIMPLE      | con   | range  | index1,index2 | index2  | 1022    | NULL           | 2    | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | e     | eq_ref | PRIMARY       | PRIMARY | 4       | se.con.EbookId | 1    |                                                                     |
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+---------------------------------------------------------------------+

[acabal]

Great!