ArtistMapper::findUniqueEntity() - Code Metrics - Inspection of "Redesign the unique constraint handling in Mappers" - owncloud/music - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( d93244...31d6f2 )

by Pauli

created 2025-06-14 19:51 UTC

ArtistMapper::findUniqueEntity() A

↳ Parent: Project

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
eloc	3
nc	1
nop	1
dl	0
loc	4
rs	10
c	0
b	0
f	0

<?php declare(strict_types=1);

/**
 * ownCloud - Music app
 *
 * This file is licensed under the Affero General Public License version 3 or
 * later. See the COPYING file.
 *
 * @author Morris Jobke <[email protected]>
 * @author Pauli Järvinen <[email protected]>
 * @copyright Morris Jobke 2013, 2014
 * @copyright Pauli Järvinen 2016 - 2025
 */

namespace OCA\Music\Db;

use OCA\Music\Utility\ArrayUtil;

use OCP\IConfig;
use OCP\IDBConnection;

/**
 * @method Artist findEntity(string $sql, array $params)
 * @method Artist[] findEntities(string $sql, array $params, ?int $limit=null, ?int $offset=null)
 * @phpstan-extends BaseMapper<Artist>
 */
class ArtistMapper extends BaseMapper {
	public function __construct(IDBConnection $db, IConfig $config) {
		parent::__construct($db, $config, 'music_artists', Artist::class, 'name', ['user_id', 'hash']);
	}

	/**
	 * @param string $userId
	 * @param integer $sortBy sort order of the result set
	 * @return Artist[]
	 */
	public function findAllHavingAlbums(string $userId, int $sortBy=SortBy::Name,
			?int $limit=null, ?int $offset=null, ?string $name=null, int $matchMode=MatchMode::Exact,
			?string $createdMin=null, ?string $createdMax=null, ?string $updatedMin=null, ?string $updatedMax=null) : array {
		
		$condition = $this->formatExcludeChildlessCondition();
		return $this->findAllWithCondition(
			$condition, $userId, $sortBy, $limit, $offset, $name, $matchMode, $createdMin, $createdMax, $updatedMin, $updatedMax);
	}

	/**
	 * @param string $userId
	 * @param integer $sortBy sort order of the result set
	 * @return Artist[]
	 */
	public function findAllHavingTracks(string $userId, int $sortBy=SortBy::Name,
			?int $limit=null, ?int $offset=null, ?string $name=null, int $matchMode=MatchMode::Exact,
			?string $createdMin=null, ?string $createdMax=null, ?string $updatedMin=null, ?string $updatedMax=null) : array {
		
		$condition = 'EXISTS (SELECT 1 FROM `*PREFIX*music_tracks` `track` WHERE `*PREFIX*music_artists`.`id` = `track`.`artist_id`)';
		return $this->findAllWithCondition(
			$condition, $userId, $sortBy, $limit, $offset, $name, $matchMode, $createdMin, $createdMax, $updatedMin, $updatedMax);
	}

	/**
	 * @return Artist[]
	 */
	private function findAllWithCondition(
			string $condition, string $userId, int $sortBy, ?int $limit, ?int $offset,
			?string $name, int $matchMode, ?string $createdMin, ?string $createdMax,
			?string $updatedMin, ?string $updatedMax) : array {
		$params = [$userId];

		if ($name !== null) {
			[$nameCond, $nameParams] = $this->formatNameConditions($name, $matchMode);
			$condition .= " AND $nameCond";
			$params = \array_merge($params, $nameParams);
		}

		[$timestampConds, $timestampParams] = $this->formatTimestampConditions($createdMin, $createdMax, $updatedMin, $updatedMax);
		if (!empty($timestampConds)) {
			$condition .= " AND $timestampConds";
			$params = \array_merge($params, $timestampParams);
		}

		$sql = $this->selectUserEntities($condition, $this->formatSortingClause($sortBy));

		return $this->findEntities($sql, $params, $limit, $offset);
	}

	/**
	 * @param int $genreId
	 * @param string $userId
	 * @param int|null $limit
	 * @param int|null $offset
	 * @return Artist[]
	 */
	public function findAllByGenre(int $genreId, string $userId, ?int $limit=null, ?int $offset=null) : array {
		$sql = $this->selectUserEntities('EXISTS '.
				'(SELECT 1 FROM `*PREFIX*music_tracks` `track`
				  WHERE `*PREFIX*music_artists`.`id` = `track`.`artist_id`
				  AND `track`.`genre_id` = ?)');

		$params = [$userId, $genreId];
		return $this->findEntities($sql, $params, $limit, $offset);
	}

	/**
	 * returns summed track play counts of each artist of the user, omitting artists which have never been played
	 *
	 * @return array [int => int], keys are artist IDs and values are play count sums; ordered largest counts first
	 */
	public function getArtistTracksPlayCount(string $userId, ?int $limit=null, ?int $offset=null) : array {
		$sql = 'SELECT `artist_id`, SUM(`play_count`) AS `sum_count`
				FROM `*PREFIX*music_tracks`
				WHERE `user_id` = ? AND `play_count` > 0
				GROUP BY `artist_id`
				ORDER BY `sum_count` DESC, `artist_id`'; // the second criterion is just to make the order predictable on even counts

		$result = $this->execute($sql, [$userId], $limit, $offset);
		$playCountByArtist = [];
		while ($row = $result->fetch()) {
			$playCountByArtist[$row['artist_id']] = (int)$row['sum_count'];
		}
		return $playCountByArtist;
	}

	/**
	 * returns the latest play time of each artist of the user, omitting artists which have never been played
	 *
	 * @return array [int => string], keys are artist IDs and values are date-times; ordered latest times first
	 */
	public function getLatestArtistPlayTimes(string $userId, ?int $limit=null, ?int $offset=null) : array {
		$sql = 'SELECT `artist_id`, MAX(`last_played`) AS `latest_time`
				FROM `*PREFIX*music_tracks`
				WHERE `user_id` = ? AND `last_played` IS NOT NULL
				GROUP BY `artist_id`
				ORDER BY `latest_time` DESC';

		$result = $this->execute($sql, [$userId], $limit, $offset);
		$latestTimeByArtist = [];
		while ($row = $result->fetch()) {
			$latestTimeByArtist[$row['artist_id']] = $row['latest_time'];
		}
		return $latestTimeByArtist;
	}

	/**
	 * returns the latest play time of each artist of the user, including artists which have never been played
	 *
	 * @return array [int => ?string], keys are artist IDs and values are date-times (or null for never played);
	 *									ordered furthest times first
	 */
	public function getFurthestArtistPlayTimes(string $userId, ?int $limit=null, ?int $offset=null) : array {
		$sql = 'SELECT `artist_id`, MAX(`last_played`) AS `latest_time`
				FROM `*PREFIX*music_tracks`
				WHERE `user_id` = ?
				GROUP BY `artist_id`
				ORDER BY `latest_time` ASC';

		$result = $this->execute($sql, [$userId], $limit, $offset);
		$latestTimeByArtist = [];
		while ($row = $result->fetch()) {
			$latestTimeByArtist[$row['artist_id']] = $row['latest_time'];
		}
		return $latestTimeByArtist;
	}

	/**
	 * @param integer[] $coverFileIds
	 * @param string[]|null $userIds the users whose music library is targeted; all users are targeted if omitted
	 * @return Artist[] artists which got modified (with incomplete data, only id and user are valid),
	 *         empty array if none
	 */
	public function removeCovers(array $coverFileIds, ?array $userIds=null) : array {
		// find albums using the given file as cover
		$sql = 'SELECT `id`, `user_id` FROM `*PREFIX*music_artists` WHERE `cover_file_id` IN ' .
		$this->questionMarks(\count($coverFileIds));
		$params = $coverFileIds;
		if ($userIds !== null) {
			$sql .= ' AND `user_id` IN ' . $this->questionMarks(\count($userIds));
			$params = \array_merge($params, $userIds);
		}
		$artists = $this->findEntities($sql, $params);

		// if any artists found, remove the cover from those
		$count = \count($artists);
		if ($count) {
			$sql = 'UPDATE `*PREFIX*music_artists`
					SET `cover_file_id` = NULL
					WHERE `id` IN ' . $this->questionMarks($count);
			$params = ArrayUtil::extractIds($artists);
			$this->execute($sql, $params);
		}

		return $artists;
	}

	/**
	 * Overridden from the base implementation
	 *
	 * @see BaseMapper::formatExcludeChildlessCondition()
	 */
	protected function formatExcludeChildlessCondition() : string {
		return 'EXISTS (SELECT 1 FROM `*PREFIX*music_albums` `album` WHERE `*PREFIX*music_artists`.`id` = `album`.`album_artist_id`)';
	}

	/**
	 * Overridden from the base implementation to provide support for table-specific rules
	 *
	 * {@inheritdoc}
	 * @see BaseMapper::advFormatSqlCondition()
	 */
	protected function advFormatSqlCondition(string $rule, string $sqlOp, string $conv) : string {
		// The extra subquery "mysqlhack" seen around some nested queries is needed in order for these to not be insanely slow on MySQL.
		// In case of 'recent_played', the MySQL 5.5.62 errored with "1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" without the extra subquery.
		$condForRule = [
			'album'			=> "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*music_albums` `a` ON `t`.`album_id` = `a`.`id` WHERE $conv(`a`.`name`) $sqlOp $conv(?))",
			'song'			=> "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` WHERE $conv(`t`.`title`) $sqlOp $conv(?))",
			'songrating'	=> "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` WHERE `t`.`rating` $sqlOp ?)",
			'albumrating'	=> "`*PREFIX*music_artists`.`id` IN (SELECT `album_artist_id` from `*PREFIX*music_albums` `al` WHERE `al`.`rating` $sqlOp ?)",
			'played_times'	=> "`*PREFIX*music_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` from `*PREFIX*music_tracks` GROUP BY `artist_id` HAVING SUM(`play_count`) $sqlOp ?) mysqlhack)",
			'last_play'		=> "`*PREFIX*music_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` from `*PREFIX*music_tracks` GROUP BY `artist_id` HAVING MAX(`last_played`) $sqlOp ?) mysqlhack)",
			'myplayed'		=> "`*PREFIX*music_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` from `*PREFIX*music_tracks` GROUP BY `artist_id` HAVING MAX(`last_played`) $sqlOp) mysqlhack)", // operator "IS NULL" or "IS NOT NULL"
			'album_count'	=> "`*PREFIX*music_artists`.`id` IN (SELECT `id` FROM `*PREFIX*music_artists` JOIN (SELECT `*PREFIX*music_artists`.`id` AS `id2`, " . $this->sqlCoalesce('`count1`', '0') . " `count2` FROM `*PREFIX*music_artists` LEFT JOIN (SELECT `*PREFIX*music_albums`.`album_artist_id` AS `id1`, COUNT(`*PREFIX*music_albums`.`id`) AS `count1` FROM `*PREFIX*music_albums` GROUP BY `*PREFIX*music_albums`.`album_artist_id`) `sub1` ON `*PREFIX*music_artists`.`id` = `id1`) `sub2` ON `*PREFIX*music_artists`.`id` = `id2` WHERE `count2` $sqlOp ?)",
			'song_count'	=> "`*PREFIX*music_artists`.`id` IN (SELECT `id` FROM `*PREFIX*music_artists` JOIN (SELECT `*PREFIX*music_artists`.`id` AS `id2`, " . $this->sqlCoalesce('`count1`', '0') . " `count2` FROM `*PREFIX*music_artists` LEFT JOIN (SELECT `*PREFIX*music_tracks`.`artist_id` AS `id1`, COUNT(`*PREFIX*music_tracks`.`id`) AS `count1` FROM `*PREFIX*music_tracks` GROUP BY `*PREFIX*music_tracks`.`artist_id`) `sub1` ON `*PREFIX*music_artists`.`id` = `id1`) `sub2` ON `*PREFIX*music_artists`.`id` = `id2` WHERE `count2` $sqlOp ?)",
			'time'			=> "`*PREFIX*music_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` FROM `*PREFIX*music_tracks` GROUP BY `artist_id` HAVING SUM(`length`) $sqlOp ?) mysqlhack)",
			'artist_genre'	=> "`*PREFIX*music_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*music_genres` `g` ON `t`.`genre_id` = `g`.`id` GROUP BY `artist_id` HAVING $conv(" . $this->sqlGroupConcat('`g`.`name`') . ") $sqlOp $conv(?)) mysqlhack)",
			'song_genre'	=> "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*music_genres` `g` ON `t`.`genre_id` = `g`.`id` WHERE $conv(`g`.`name`) $sqlOp $conv(?))",
			'no_genre'		=> "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*music_genres` `g` ON `t`.`genre_id` = `g`.`id` WHERE `g`.`name` " . (($sqlOp == 'IS NOT NULL') ? '=' : '!=') . ' "")',
			'playlist'		=> "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` WHERE $sqlOp EXISTS (SELECT 1 from `*PREFIX*music_playlists` `p` WHERE `p`.`id` = ? AND `p`.`track_ids` LIKE " . $this->sqlConcat("'%|'", "`t`.`id`", "'|%'") . '))',
			'playlist_name'	=> "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` WHERE EXISTS (SELECT 1 from `*PREFIX*music_playlists` `p` WHERE $conv(`p`.`name`) $sqlOp $conv(?) AND `p`.`track_ids` LIKE " . $this->sqlConcat("'%|'", "`t`.`id`", "'|%'") . '))',
			'file'			=> "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` JOIN `*PREFIX*filecache` `f` ON `t`.`file_id` = `f`.`fileid` WHERE $conv(`f`.`name`) $sqlOp $conv(?))",
			'recent_played'	=> "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM (SELECT `artist_id`, MAX(`last_played`) FROM `*PREFIX*music_tracks` WHERE `user_id` = ? GROUP BY `artist_id` ORDER BY MAX(`last_played`) DESC LIMIT $sqlOp) mysqlhack)",
			'mbid_song'		=> "`*PREFIX*music_artists`.`id` IN (SELECT `artist_id` FROM `*PREFIX*music_tracks` `t` WHERE `t`.`mbid` $sqlOp ?)",
			'mbid_album'	=> "`*PREFIX*music_artists`.`id` IN (SELECT `album_artist_id` from `*PREFIX*music_albums` `al` WHERE `al`.`mbid` $sqlOp ?)",
			'has_image'		=> "`cover_file_id` $sqlOp" // operator "IS NULL" or "IS NOT NULL"
		];

		// Add alias rules
		$condForRule['played'] = $condForRule['myplayed']; // we give no access to other people's data; not part of the API spec but Ample uses this
		$condForRule['genre'] = $condForRule['artist_genre'];
		$condForRule['artist'] = parent::advFormatSqlCondition('title', $sqlOp, $conv);
		$condForRule['mbid_artist'] = parent::advFormatSqlCondition('mbid', $sqlOp, $conv);

		return $condForRule[$rule] ?? parent::advFormatSqlCondition($rule, $sqlOp, $conv);
	}
}


1			<?php declare(strict_types=1);
2
3			/**
4			* ownCloud - Music app
5			*
6			* This file is licensed under the Affero General Public License version 3 or
7			* later. See the COPYING file.
8			*
9			* @author Morris Jobke <[email protected]>
10			* @author Pauli Järvinen <[email protected]>
11			* @copyright Morris Jobke 2013, 2014
12			* @copyright Pauli Järvinen 2016 - 2025
13			*/
14
15			namespace OCA\Music\Db;
16
17			use OCA\Music\Utility\ArrayUtil;
18
19			use OCP\IConfig;
20			use OCP\IDBConnection;
21
22			/**
23			* @method Artist findEntity(string $sql, array $params)
24			* @method Artist[] findEntities(string $sql, array $params, ?int $limit=null, ?int $offset=null)
25			* @phpstan-extends BaseMapper<Artist>
26			*/
27			class ArtistMapper extends BaseMapper {
28			public function __construct(IDBConnection $db, IConfig $config) {
29			parent::__construct($db, $config, 'music_artists', Artist::class, 'name', ['user_id', 'hash']);
30			}
31
32			/**
33			* @param string $userId
34			* @param integer $sortBy sort order of the result set
35			* @return Artist[]
36			*/
37			public function findAllHavingAlbums(string $userId, int $sortBy=SortBy::Name,
38			?int $limit=null, ?int $offset=null, ?string $name=null, int $matchMode=MatchMode::Exact,
39			?string $createdMin=null, ?string $createdMax=null, ?string $updatedMin=null, ?string $updatedMax=null) : array {
40
41			$condition = $this->formatExcludeChildlessCondition();
42			return $this->findAllWithCondition(
43			$condition, $userId, $sortBy, $limit, $offset, $name, $matchMode, $createdMin, $createdMax, $updatedMin, $updatedMax);
44			}
45
46			/**
47			* @param string $userId
48			* @param integer $sortBy sort order of the result set
49			* @return Artist[]
50			*/
51			public function findAllHavingTracks(string $userId, int $sortBy=SortBy::Name,
52			?int $limit=null, ?int $offset=null, ?string $name=null, int $matchMode=MatchMode::Exact,
53			?string $createdMin=null, ?string $createdMax=null, ?string $updatedMin=null, ?string $updatedMax=null) : array {
54
55			$condition = 'EXISTS (SELECT 1 FROM `PREFIXmusic_tracks` `track` WHERE `PREFIXmusic_artists`.`id` = `track`.`artist_id`)';
56			return $this->findAllWithCondition(
57			$condition, $userId, $sortBy, $limit, $offset, $name, $matchMode, $createdMin, $createdMax, $updatedMin, $updatedMax);
58			}
59
60			/**
61			* @return Artist[]
62			*/
63			private function findAllWithCondition(
64			string $condition, string $userId, int $sortBy, ?int $limit, ?int $offset,
65			?string $name, int $matchMode, ?string $createdMin, ?string $createdMax,
66			?string $updatedMin, ?string $updatedMax) : array {
67			$params = [$userId];
68
69			if ($name !== null) {
70			[$nameCond, $nameParams] = $this->formatNameConditions($name, $matchMode);
71			$condition .= " AND $nameCond";
72			$params = \array_merge($params, $nameParams);
73			}
74
75			[$timestampConds, $timestampParams] = $this->formatTimestampConditions($createdMin, $createdMax, $updatedMin, $updatedMax);
76			if (!empty($timestampConds)) {
77			$condition .= " AND $timestampConds";
78			$params = \array_merge($params, $timestampParams);
79			}
80
81			$sql = $this->selectUserEntities($condition, $this->formatSortingClause($sortBy));
82
83			return $this->findEntities($sql, $params, $limit, $offset);
84			}
85
86			/**
87			* @param int $genreId
88			* @param string $userId
89			* @param int\|null $limit
90			* @param int\|null $offset
91			* @return Artist[]
92			*/
93			public function findAllByGenre(int $genreId, string $userId, ?int $limit=null, ?int $offset=null) : array {
94			$sql = $this->selectUserEntities('EXISTS '.
95			'(SELECT 1 FROM `PREFIXmusic_tracks` `track`
96			WHERE `PREFIXmusic_artists`.`id` = `track`.`artist_id`
97			AND `track`.`genre_id` = ?)');
98
99			$params = [$userId, $genreId];
100			return $this->findEntities($sql, $params, $limit, $offset);
101			}
102
103			/**
104			* returns summed track play counts of each artist of the user, omitting artists which have never been played
105			*
106			* @return array [int => int], keys are artist IDs and values are play count sums; ordered largest counts first
107			*/
108			public function getArtistTracksPlayCount(string $userId, ?int $limit=null, ?int $offset=null) : array {
109			$sql = 'SELECT `artist_id`, SUM(`play_count`) AS `sum_count`
110			FROM `PREFIXmusic_tracks`
111			WHERE `user_id` = ? AND `play_count` > 0
112			GROUP BY `artist_id`
113			ORDER BY `sum_count` DESC, `artist_id`'; // the second criterion is just to make the order predictable on even counts
114
115			$result = $this->execute($sql, [$userId], $limit, $offset);
116			$playCountByArtist = [];
117			while ($row = $result->fetch()) {
118			$playCountByArtist[$row['artist_id']] = (int)$row['sum_count'];
119			}
120			return $playCountByArtist;
121			}
122
123			/**
124			* returns the latest play time of each artist of the user, omitting artists which have never been played
125			*
126			* @return array [int => string], keys are artist IDs and values are date-times; ordered latest times first
127			*/
128			public function getLatestArtistPlayTimes(string $userId, ?int $limit=null, ?int $offset=null) : array {
129			$sql = 'SELECT `artist_id`, MAX(`last_played`) AS `latest_time`
130			FROM `PREFIXmusic_tracks`
131			WHERE `user_id` = ? AND `last_played` IS NOT NULL
132			GROUP BY `artist_id`
133			ORDER BY `latest_time` DESC';
134
135			$result = $this->execute($sql, [$userId], $limit, $offset);
136			$latestTimeByArtist = [];
137			while ($row = $result->fetch()) {
138			$latestTimeByArtist[$row['artist_id']] = $row['latest_time'];
139			}
140			return $latestTimeByArtist;
141			}
142
143			/**
144			* returns the latest play time of each artist of the user, including artists which have never been played
145			*
146			* @return array [int => ?string], keys are artist IDs and values are date-times (or null for never played);
147			* ordered furthest times first
148			*/
149			public function getFurthestArtistPlayTimes(string $userId, ?int $limit=null, ?int $offset=null) : array {
150			$sql = 'SELECT `artist_id`, MAX(`last_played`) AS `latest_time`
151			FROM `PREFIXmusic_tracks`
152			WHERE `user_id` = ?
153			GROUP BY `artist_id`
154			ORDER BY `latest_time` ASC';
155
156			$result = $this->execute($sql, [$userId], $limit, $offset);
157			$latestTimeByArtist = [];
158			while ($row = $result->fetch()) {
159			$latestTimeByArtist[$row['artist_id']] = $row['latest_time'];
160			}
161			return $latestTimeByArtist;
162			}
163
164			/**
165			* @param integer[] $coverFileIds
166			* @param string[]\|null $userIds the users whose music library is targeted; all users are targeted if omitted
167			* @return Artist[] artists which got modified (with incomplete data, only id and user are valid),
168			* empty array if none
169			*/
170			public function removeCovers(array $coverFileIds, ?array $userIds=null) : array {
171			// find albums using the given file as cover
172			$sql = 'SELECT `id`, `user_id` FROM `PREFIXmusic_artists` WHERE `cover_file_id` IN ' .
173			$this->questionMarks(\count($coverFileIds));
174			$params = $coverFileIds;
175			if ($userIds !== null) {
176			$sql .= ' AND `user_id` IN ' . $this->questionMarks(\count($userIds));
177			$params = \array_merge($params, $userIds);
178			}
179			$artists = $this->findEntities($sql, $params);
180
181			// if any artists found, remove the cover from those
182			$count = \count($artists);
183			if ($count) {
184			$sql = 'UPDATE `PREFIXmusic_artists`
185			SET `cover_file_id` = NULL
186			WHERE `id` IN ' . $this->questionMarks($count);
187			$params = ArrayUtil::extractIds($artists);
188			$this->execute($sql, $params);
189			}
190
191			return $artists;
192			}
193
194			/**
195			* Overridden from the base implementation
196			*
197			* @see BaseMapper::formatExcludeChildlessCondition()
198			*/
199			protected function formatExcludeChildlessCondition() : string {
200			return 'EXISTS (SELECT 1 FROM `PREFIXmusic_albums` `album` WHERE `PREFIXmusic_artists`.`id` = `album`.`album_artist_id`)';
201			}
202
203			/**
204			* Overridden from the base implementation to provide support for table-specific rules
205			*
206			* {@inheritdoc}
207			* @see BaseMapper::advFormatSqlCondition()
208			*/
209			protected function advFormatSqlCondition(string $rule, string $sqlOp, string $conv) : string {
210			// The extra subquery "mysqlhack" seen around some nested queries is needed in order for these to not be insanely slow on MySQL.
211			// In case of 'recent_played', the MySQL 5.5.62 errored with "1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" without the extra subquery.
212			$condForRule = [
213			'album' => "`PREFIXmusic_artists`.`id` IN (SELECT `artist_id` FROM `PREFIXmusic_tracks` `t` JOIN `PREFIXmusic_albums` `a` ON `t`.`album_id` = `a`.`id` WHERE $conv(`a`.`name`) $sqlOp $conv(?))",
214			'song' => "`PREFIXmusic_artists`.`id` IN (SELECT `artist_id` FROM `PREFIXmusic_tracks` `t` WHERE $conv(`t`.`title`) $sqlOp $conv(?))",
215			'songrating' => "`PREFIXmusic_artists`.`id` IN (SELECT `artist_id` FROM `PREFIXmusic_tracks` `t` WHERE `t`.`rating` $sqlOp ?)",
216			'albumrating' => "`PREFIXmusic_artists`.`id` IN (SELECT `album_artist_id` from `PREFIXmusic_albums` `al` WHERE `al`.`rating` $sqlOp ?)",
217			'played_times' => "`PREFIXmusic_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` from `PREFIXmusic_tracks` GROUP BY `artist_id` HAVING SUM(`play_count`) $sqlOp ?) mysqlhack)",
218			'last_play' => "`PREFIXmusic_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` from `PREFIXmusic_tracks` GROUP BY `artist_id` HAVING MAX(`last_played`) $sqlOp ?) mysqlhack)",
219			'myplayed' => "`PREFIXmusic_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` from `PREFIXmusic_tracks` GROUP BY `artist_id` HAVING MAX(`last_played`) $sqlOp) mysqlhack)", // operator "IS NULL" or "IS NOT NULL"
220			'album_count' => "`PREFIXmusic_artists`.`id` IN (SELECT `id` FROM `PREFIXmusic_artists` JOIN (SELECT `PREFIXmusic_artists`.`id` AS `id2`, " . $this->sqlCoalesce('`count1`', '0') . " `count2` FROM `PREFIXmusic_artists` LEFT JOIN (SELECT `PREFIXmusic_albums`.`album_artist_id` AS `id1`, COUNT(`PREFIXmusic_albums`.`id`) AS `count1` FROM `PREFIXmusic_albums` GROUP BY `PREFIXmusic_albums`.`album_artist_id`) `sub1` ON `PREFIXmusic_artists`.`id` = `id1`) `sub2` ON `PREFIXmusic_artists`.`id` = `id2` WHERE `count2` $sqlOp ?)",
221			'song_count' => "`PREFIXmusic_artists`.`id` IN (SELECT `id` FROM `PREFIXmusic_artists` JOIN (SELECT `PREFIXmusic_artists`.`id` AS `id2`, " . $this->sqlCoalesce('`count1`', '0') . " `count2` FROM `PREFIXmusic_artists` LEFT JOIN (SELECT `PREFIXmusic_tracks`.`artist_id` AS `id1`, COUNT(`PREFIXmusic_tracks`.`id`) AS `count1` FROM `PREFIXmusic_tracks` GROUP BY `PREFIXmusic_tracks`.`artist_id`) `sub1` ON `PREFIXmusic_artists`.`id` = `id1`) `sub2` ON `PREFIXmusic_artists`.`id` = `id2` WHERE `count2` $sqlOp ?)",
222			'time' => "`PREFIXmusic_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` FROM `PREFIXmusic_tracks` GROUP BY `artist_id` HAVING SUM(`length`) $sqlOp ?) mysqlhack)",
223			'artist_genre' => "`PREFIXmusic_artists`.`id` IN (SELECT * FROM (SELECT `artist_id` FROM `PREFIXmusic_tracks` `t` JOIN `PREFIXmusic_genres` `g` ON `t`.`genre_id` = `g`.`id` GROUP BY `artist_id` HAVING $conv(" . $this->sqlGroupConcat('`g`.`name`') . ") $sqlOp $conv(?)) mysqlhack)",
224			'song_genre' => "`PREFIXmusic_artists`.`id` IN (SELECT `artist_id` FROM `PREFIXmusic_tracks` `t` JOIN `PREFIXmusic_genres` `g` ON `t`.`genre_id` = `g`.`id` WHERE $conv(`g`.`name`) $sqlOp $conv(?))",
225			'no_genre' => "`PREFIXmusic_artists`.`id` IN (SELECT `artist_id` FROM `PREFIXmusic_tracks` `t` JOIN `PREFIXmusic_genres` `g` ON `t`.`genre_id` = `g`.`id` WHERE `g`.`name` " . (($sqlOp == 'IS NOT NULL') ? '=' : '!=') . ' "")',
226			'playlist' => "`PREFIXmusic_artists`.`id` IN (SELECT `artist_id` FROM `PREFIXmusic_tracks` `t` WHERE $sqlOp EXISTS (SELECT 1 from `PREFIXmusic_playlists` `p` WHERE `p`.`id` = ? AND `p`.`track_ids` LIKE " . $this->sqlConcat("'%\|'", "`t`.`id`", "'\|%'") . '))',
227			'playlist_name' => "`PREFIXmusic_artists`.`id` IN (SELECT `artist_id` FROM `PREFIXmusic_tracks` `t` WHERE EXISTS (SELECT 1 from `PREFIXmusic_playlists` `p` WHERE $conv(`p`.`name`) $sqlOp $conv(?) AND `p`.`track_ids` LIKE " . $this->sqlConcat("'%\|'", "`t`.`id`", "'\|%'") . '))',
228			'file' => "`PREFIXmusic_artists`.`id` IN (SELECT `artist_id` FROM `PREFIXmusic_tracks` `t` JOIN `PREFIXfilecache` `f` ON `t`.`file_id` = `f`.`fileid` WHERE $conv(`f`.`name`) $sqlOp $conv(?))",
229			'recent_played' => "`PREFIXmusic_artists`.`id` IN (SELECT `artist_id` FROM (SELECT `artist_id`, MAX(`last_played`) FROM `PREFIXmusic_tracks` WHERE `user_id` = ? GROUP BY `artist_id` ORDER BY MAX(`last_played`) DESC LIMIT $sqlOp) mysqlhack)",
230			'mbid_song' => "`PREFIXmusic_artists`.`id` IN (SELECT `artist_id` FROM `PREFIXmusic_tracks` `t` WHERE `t`.`mbid` $sqlOp ?)",
231			'mbid_album' => "`PREFIXmusic_artists`.`id` IN (SELECT `album_artist_id` from `PREFIXmusic_albums` `al` WHERE `al`.`mbid` $sqlOp ?)",
232			'has_image' => "`cover_file_id` $sqlOp" // operator "IS NULL" or "IS NOT NULL"
233			];
234
235			// Add alias rules
236			$condForRule['played'] = $condForRule['myplayed']; // we give no access to other people's data; not part of the API spec but Ample uses this
237			$condForRule['genre'] = $condForRule['artist_genre'];
238			$condForRule['artist'] = parent::advFormatSqlCondition('title', $sqlOp, $conv);
239			$condForRule['mbid_artist'] = parent::advFormatSqlCondition('mbid', $sqlOp, $conv);
240
241			return $condForRule[$rule] ?? parent::advFormatSqlCondition($rule, $sqlOp, $conv);
242			}
243			}
244

owncloud / music

Push — master ( d93244...31d6f2 )

ArtistMapper::findUniqueEntity() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like