Fix broken tests for endpoint to get a torrent list

[josecelano]

The PR #162 was merged and today I've realized there were some broken tests.

test e2e::contexts::torrent::contract::for_guests::it_should_allow_guests_to_get_torrents ... FAILED
test e2e::contexts::torrent::contract::for_guests::it_should_allow_to_get_torrents_with_pagination ... FAILED
test e2e::contexts::torrent::contract::for_guests::it_should_return_a_default_amount_of_torrents_per_request_if_no_page_size_is_provided ... FAILED
test e2e::contexts::torrent::contract::for_guests::it_should_allow_to_limit_the_number_of_torrents_per_request ... FAILED

It seems the pagination has been broken with the changes introduced to support "tags".

See https://github.com/torrust/torrust-index-backend/actions/runs/5209788641/jobs/9400067078#step:13:1336

[josecelano]

There is an error with a SQL query in SQLite:

SELECT
  COUNT(*) as count
FROM
  (
    SELECT
      tt.torrent_id,
      tp.username AS uploader,
      tt.info_hash,
      ti.title,
      ti.description,
      tt.category_id,
      DATE_FORMAT(tt.date_uploaded, '%Y-%m-%d %H:%i:%s') AS date_uploaded,
      tt.size AS file_size,
      CAST(COALESCE(sum(ts.seeders), 0) as signed) as seeders,
      CAST(COALESCE(sum(ts.leechers), 0) as signed) as leechers
    FROM
      torrust_torrents tt
      INNER JOIN torrust_user_profiles tp ON tt.uploader_id = tp.user_id
      INNER JOIN torrust_torrent_info ti ON tt.torrent_id = ti.torrent_id
      LEFT JOIN torrust_torrent_tracker_stats ts ON tt.torrent_id = ts.torrent_id
    WHERE
      title LIKE ?
    GROUP BY
      tt.torrent_id
  ) AS count_table

DATE_FORMAT does not exist in SQLite, and it's not needed. The correct query is:

SELECT
  COUNT(*) as count
FROM
  (
    SELECT
      tt.torrent_id,
      tp.username AS uploader,
      tt.info_hash,
      ti.title,
      ti.description,
      tt.category_id,
      tt.date_uploaded,
      tt.size AS file_size,
      CAST(COALESCE(sum(ts.seeders), 0) as signed) as seeders,
      CAST(COALESCE(sum(ts.leechers), 0) as signed) as leechers
    FROM
      torrust_torrents tt
      INNER JOIN torrust_user_profiles tp ON tt.uploader_id = tp.user_id
      INNER JOIN torrust_torrent_info ti ON tt.torrent_id = ti.torrent_id
      LEFT JOIN torrust_torrent_tracker_stats ts ON tt.torrent_id = ts.torrent_id
    WHERE
      title LIKE ?
    GROUP BY
      tt.torrent_id
  ) AS count_table