25261295508

Committed 02 May 2026 08:35PM UTC coverage: 86.464% (+0.06%) from 86.401%

Build # 25261295508

Build Type

Pull #1556

github

Committed by

ClemRz

Commit Message

feat(qualitySort): add sort and order query params to with-quality endpoints

Pull Request Pull Request #1556: feat(qualitySort): add sort and order query params to with-quality endpoints

Coverage Stats

2994 of 3606 branches covered (83.03%)

Branch coverage included in aggregate %.

50 of 52 new or added lines in 5 files covered. (96.15%)

2 existing lines in 1 file now uncovered.

6198 of 7025 relevant lines covered (88.23%)

52.37 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

91.67

/api/services/DataQualityComputeService.js

const CommonService = require('./CommonService');

/**
 * this service is used to retrieves the elements included
 * in the computation of the quality of the data of an entrance.
 */

/**
 * Injects an ORDER BY clause before the LIMIT clause when sort is provided.
 * The sort column and order direction are interpolated directly into the SQL
 * string — safe because allow-list validation has already occurred upstream.
 *
 * @param {string} sql - SQL query string containing a LIMIT clause
 * @param {string|null} sort - validated column name
 * @param {string|null} order - 'asc' or 'desc'
 * @returns {string} SQL with optional ORDER BY injected
 */
const applySort = (sql, sort, order) => {
  if (!sort) return sql;
  const orderBy = `ORDER BY ${sort} ${order.toUpperCase()} `;
  return sql.replace('LIMIT', `${orderBy}LIMIT`);
};

const GET_ENTRANCES_WITH_QUALITY_BY_MASSIF = `
  SELECT *
  FROM v_data_quality_compute_entrance
  WHERE id_massif = $1
  LIMIT $2 OFFSET $3
`;

const GET_ENTRANCES_WITH_QUALITY_BY_COUNTRY = `
  SELECT DISTINCT id_entrance, general_latest_date_of_update, general_nb_contributions, location_latest_date_of_update, location_nb_contributions, description_latest_date_of_update, description_nb_contributions, document_latest_date_of_update, document_nb_contributions, rigging_latest_date_of_update, rigging_nb_contributions, history_latest_date_of_update, history_nb_contributions, comment_latest_date_of_update, comment_nb_contributions, entrance_name, id_country, country_name, date_of_update
  FROM v_data_quality_compute_entrance
  WHERE id_country = $1
  LIMIT $2 OFFSET $3
`;

const GET_ENTRANCES_WITH_QUALITY_BY_REGION = `
  SELECT DISTINCT v.id_entrance, v.general_latest_date_of_update, v.general_nb_contributions, v.location_latest_date_of_update, v.location_nb_contributions, v.description_latest_date_of_update, v.description_nb_contributions, v.document_latest_date_of_update, v.document_nb_contributions, v.rigging_latest_date_of_update, v.rigging_nb_contributions, v.history_latest_date_of_update, v.history_nb_contributions, v.comment_latest_date_of_update, v.comment_nb_contributions, v.entrance_name, v.id_country, v.country_name, v.date_of_update
  FROM v_data_quality_compute_entrance v
  JOIN t_entrance e ON v.id_entrance = e.id
  WHERE e.iso_3166_2 = $1
  LIMIT $2 OFFSET $3
`;

const COUNT_ENTRANCES_WITH_QUALITY_BY_MASSIF = `
  SELECT COUNT(*)
  FROM v_data_quality_compute_entrance
  WHERE id_massif = $1
`;

const COUNT_ENTRANCES_WITH_QUALITY_BY_COUNTRY = `
  SELECT COUNT(DISTINCT id_entrance)
  FROM v_data_quality_compute_entrance
  WHERE id_country = $1
`;

const COUNT_ENTRANCES_WITH_QUALITY_BY_REGION = `
  SELECT COUNT(DISTINCT v.id_entrance)
  FROM v_data_quality_compute_entrance v
  JOIN t_entrance e ON v.id_entrance = e.id
  WHERE e.iso_3166_2 = $1
`;

module.exports = {
  /**
   *
   * @param {int} massifId
   * @param {int} limit
   * @param {int} offset
   * @param {string|null} sort - validated column name to sort by
   * @param {string|null} order - 'asc' or 'desc'
   * @returns {Object} the date of the latest update and the number of contributions on all entrances in a massif
   *          or null if no result or something went wrong
   */
  getEntrancesWithQualityByMassif: async (
    massifId,
    limit = null,
    offset = null,
    sort = null,
    order = null
  ) => {
    try {
      const params =
        limit !== null && offset !== null
          ? [massifId, limit, offset]
          : [massifId];
      let query =
        limit !== null && offset !== null
          ? GET_ENTRANCES_WITH_QUALITY_BY_MASSIF
          : GET_ENTRANCES_WITH_QUALITY_BY_MASSIF.replace(
              'LIMIT $2 OFFSET $3',
              ''
            );
      query = applySort(query, sort, order);
      const queryResult = await CommonService.query(query, params);
      return queryResult.rows;
    } catch (e) {
      sails.log.error(e);
      return null;
    }
  },

  /**
   *
   * @param {int} massifId
   * @returns {int} count of entrances in massif
   */
  getEntrancesWithQualityByMassifCount: async (massifId) => {
    try {
      const queryResult = await CommonService.query(
        COUNT_ENTRANCES_WITH_QUALITY_BY_MASSIF,
        [massifId]
      );
      return parseInt(queryResult.rows[0].count, 10);
    } catch (e) {
      sails.log.error(e);
      return 0;
    }
  },

  /**
   *
   * @param {string} countryId alpha-2 code
   * @param {int} limit
   * @param {int} offset
   * @param {string|null} sort - validated column name to sort by
   * @param {string|null} order - 'asc' or 'desc'
   * @returns {Object} the date of the latest update and the number of contributions on all entrances in a country
   *          or null if no result or something went wrong
   */
  getEntrancesWithQualityByCountry: async (
    countryId,
    limit = null,
    offset = null,
    sort = null,
    order = null
  ) => {
    try {
      const params =
        limit !== null && offset !== null
          ? [countryId, limit, offset]
          : [countryId];
      let query =
        limit !== null && offset !== null
          ? GET_ENTRANCES_WITH_QUALITY_BY_COUNTRY
          : GET_ENTRANCES_WITH_QUALITY_BY_COUNTRY.replace(
              'LIMIT $2 OFFSET $3',
              ''
            );
      query = applySort(query, sort, order);
      const queryResult = await CommonService.query(query, params);
      return queryResult.rows;
    } catch (e) {
      sails.log.error(e);
      return null;
    }
  },

  /**
   *
   * @param {string} countryId alpha-2 code
   * @returns {int} count of entrances in country
   */
  getEntrancesWithQualityByCountryCount: async (countryId) => {
    try {
      const queryResult = await CommonService.query(
        COUNT_ENTRANCES_WITH_QUALITY_BY_COUNTRY,
        [countryId]
      );
      return parseInt(queryResult.rows[0].count, 10);
    } catch (e) {
      sails.log.error(e);
      return 0;
    }
  },

  /**
   *
   * @param {string} regionId ISO 3166-2 code (e.g., 'US-TN')
   * @param {int} limit
   * @param {int} offset
   * @param {string|null} sort - validated column name to sort by
   * @param {string|null} order - 'asc' or 'desc'
   * @returns {Object} the date of the latest update and the number of contributions on all entrances in a region
   *          or null if no result or something went wrong
   */
  getEntrancesWithQualityByRegion: async (
    regionId,
    limit = null,
    offset = null,
    sort = null,
    order = null
  ) => {
    try {
      const params =
        limit !== null && offset !== null
          ? [regionId, limit, offset]
          : [regionId];
      let query =
        limit !== null && offset !== null
          ? GET_ENTRANCES_WITH_QUALITY_BY_REGION
          : GET_ENTRANCES_WITH_QUALITY_BY_REGION.replace(
              'LIMIT $2 OFFSET $3',
              ''
            );
      query = applySort(query, sort, order);
      const queryResult = await CommonService.query(query, params);
      return queryResult.rows;
    } catch (e) {
      sails.log.error(e);
      return null;
    }
  },

  /**
   *
   * @param {string} regionId ISO 3166-2 code (e.g., 'US-TN')
   * @returns {int} count of entrances in region
   */
  getEntrancesWithQualityByRegionCount: async (regionId) => {
    try {
      const queryResult = await CommonService.query(
        COUNT_ENTRANCES_WITH_QUALITY_BY_REGION,
        [regionId]
      );
      return parseInt(queryResult.rows[0].count, 10);
    } catch (e) {
      sails.log.error(e);
      return 0;
    }
  },

  /**
   *
   * @param {number} entranceId
   * @returns {Object|null} the materialized view row, or null if not found
   */
  getEntranceQualityById: async (entranceId) => {
    try {
      const queryResult = await CommonService.query(
        'SELECT * FROM v_data_quality_compute_entrance WHERE id_entrance = $1 ORDER BY id_massif NULLS LAST LIMIT 1',
        [entranceId]
      );
      return queryResult.rows[0] || null;
    } catch (e) {
      sails.log.error(e);
      return null;
    }
  },
};

1	const CommonService = require('./CommonService');	2✔
2
3	/**
4	* this service is used to retrieves the elements included
5	* in the computation of the quality of the data of an entrance.
6	*/
7
8	/**
9	* Injects an ORDER BY clause before the LIMIT clause when sort is provided.
10	* The sort column and order direction are interpolated directly into the SQL
11	* string — safe because allow-list validation has already occurred upstream.
12	*
13	* @param {string} sql - SQL query string containing a LIMIT clause
14	* @param {string\|null} sort - validated column name
15	* @param {string\|null} order - 'asc' or 'desc'
16	* @returns {string} SQL with optional ORDER BY injected
17	*/
18	const applySort = (sql, sort, order) => {	2✔
19	if (!sort) return sql;	143✔
20	const orderBy = `ORDER BY ${sort} ${order.toUpperCase()} `;	109✔
21	return sql.replace('LIMIT', `${orderBy}LIMIT`);	109✔
22	};
23
24	const GET_ENTRANCES_WITH_QUALITY_BY_MASSIF = `	2✔
25	SELECT *
26	FROM v_data_quality_compute_entrance
27	WHERE id_massif = $1
28	LIMIT $2 OFFSET $3
29	`;
30
31	const GET_ENTRANCES_WITH_QUALITY_BY_COUNTRY = `	2✔
32	SELECT DISTINCT id_entrance, general_latest_date_of_update, general_nb_contributions, location_latest_date_of_update, location_nb_contributions, description_latest_date_of_update, description_nb_contributions, document_latest_date_of_update, document_nb_contributions, rigging_latest_date_of_update, rigging_nb_contributions, history_latest_date_of_update, history_nb_contributions, comment_latest_date_of_update, comment_nb_contributions, entrance_name, id_country, country_name, date_of_update
33	FROM v_data_quality_compute_entrance
34	WHERE id_country = $1
35	LIMIT $2 OFFSET $3
36	`;
37
38	const GET_ENTRANCES_WITH_QUALITY_BY_REGION = `	2✔
39	SELECT DISTINCT v.id_entrance, v.general_latest_date_of_update, v.general_nb_contributions, v.location_latest_date_of_update, v.location_nb_contributions, v.description_latest_date_of_update, v.description_nb_contributions, v.document_latest_date_of_update, v.document_nb_contributions, v.rigging_latest_date_of_update, v.rigging_nb_contributions, v.history_latest_date_of_update, v.history_nb_contributions, v.comment_latest_date_of_update, v.comment_nb_contributions, v.entrance_name, v.id_country, v.country_name, v.date_of_update
40	FROM v_data_quality_compute_entrance v
41	JOIN t_entrance e ON v.id_entrance = e.id
42	WHERE e.iso_3166_2 = $1
43	LIMIT $2 OFFSET $3
44	`;
45
46	const COUNT_ENTRANCES_WITH_QUALITY_BY_MASSIF = `	2✔
47	SELECT COUNT(*)
48	FROM v_data_quality_compute_entrance
49	WHERE id_massif = $1
50	`;
51
52	const COUNT_ENTRANCES_WITH_QUALITY_BY_COUNTRY = `	2✔
53	SELECT COUNT(DISTINCT id_entrance)
54	FROM v_data_quality_compute_entrance
55	WHERE id_country = $1
56	`;
57
58	const COUNT_ENTRANCES_WITH_QUALITY_BY_REGION = `	2✔
59	SELECT COUNT(DISTINCT v.id_entrance)
60	FROM v_data_quality_compute_entrance v
61	JOIN t_entrance e ON v.id_entrance = e.id
62	WHERE e.iso_3166_2 = $1
63	`;
64
65	module.exports = {	2✔
66	/**
67	*
68	* @param {int} massifId
69	* @param {int} limit
70	* @param {int} offset
71	* @param {string\|null} sort - validated column name to sort by
72	* @param {string\|null} order - 'asc' or 'desc'
73	* @returns {Object} the date of the latest update and the number of contributions on all entrances in a massif
74	* or null if no result or something went wrong
75	*/
76	getEntrancesWithQualityByMassif: async (
77	massifId,
78	limit = null,	3✔
79	offset = null,	3✔
80	sort = null,	4✔
81	order = null	4✔
82	) => {
83	try {	113✔
84	const params =
85	limit !== null && offset !== null	113✔
86	? [massifId, limit, offset]
87	: [massifId];
88	let query =
89	limit !== null && offset !== null	113✔
90	? GET_ENTRANCES_WITH_QUALITY_BY_MASSIF
91	: GET_ENTRANCES_WITH_QUALITY_BY_MASSIF.replace(
92	'LIMIT $2 OFFSET $3',
93	''
94	);
95	query = applySort(query, sort, order);	113✔
96	const queryResult = await CommonService.query(query, params);	113✔
97	return queryResult.rows;	112✔
98	} catch (e) {
99	sails.log.error(e);	1✔
100	return null;	1✔
101	}
102	},
103
104	/**
105	*
106	* @param {int} massifId
107	* @returns {int} count of entrances in massif
108	*/
109	getEntrancesWithQualityByMassifCount: async (massifId) => {
110	try {	11✔
111	const queryResult = await CommonService.query(	11✔
112	COUNT_ENTRANCES_WITH_QUALITY_BY_MASSIF,
113	[massifId]
114	);
115	return parseInt(queryResult.rows[0].count, 10);	11✔
116	} catch (e) {
NEW 117	sails.log.error(e);	×
NEW 118	return 0;	×
119	}
120	},
121
122	/**
123	*
124	* @param {string} countryId alpha-2 code
125	* @param {int} limit
126	* @param {int} offset
127	* @param {string\|null} sort - validated column name to sort by
128	* @param {string\|null} order - 'asc' or 'desc'
129	* @returns {Object} the date of the latest update and the number of contributions on all entrances in a country
130	* or null if no result or something went wrong
131	*/
132	getEntrancesWithQualityByCountry: async (
133	countryId,
134	limit = null,	3✔
135	offset = null,	3✔
136	sort = null,	4✔
137	order = null	4✔
138	) => {
139	try {	13✔
140	const params =
141	limit !== null && offset !== null	13✔
142	? [countryId, limit, offset]
143	: [countryId];
144	let query =
145	limit !== null && offset !== null	13✔
146	? GET_ENTRANCES_WITH_QUALITY_BY_COUNTRY
147	: GET_ENTRANCES_WITH_QUALITY_BY_COUNTRY.replace(
148	'LIMIT $2 OFFSET $3',
149	''
150	);
151	query = applySort(query, sort, order);	13✔
152	const queryResult = await CommonService.query(query, params);	13✔
153	return queryResult.rows;	12✔
154	} catch (e) {
155	sails.log.error(e);	1✔
156	return null;	1✔
157	}
158	},
159
160	/**
161	*
162	* @param {string} countryId alpha-2 code
163	* @returns {int} count of entrances in country
164	*/
165	getEntrancesWithQualityByCountryCount: async (countryId) => {
166	try {	10✔
167	const queryResult = await CommonService.query(	10✔
168	COUNT_ENTRANCES_WITH_QUALITY_BY_COUNTRY,
169	[countryId]
170	);
171	return parseInt(queryResult.rows[0].count, 10);	10✔
172	} catch (e) {
173	sails.log.error(e);	×
174	return 0;	×
175	}
176	},
177
178	/**
179	*
180	* @param {string} regionId ISO 3166-2 code (e.g., 'US-TN')
181	* @param {int} limit
182	* @param {int} offset
183	* @param {string\|null} sort - validated column name to sort by
184	* @param {string\|null} order - 'asc' or 'desc'
185	* @returns {Object} the date of the latest update and the number of contributions on all entrances in a region
186	* or null if no result or something went wrong
187	*/
188	getEntrancesWithQualityByRegion: async (
189	regionId,
190	limit = null,	3✔
191	offset = null,	3✔
192	sort = null,	4✔
193	order = null	4✔
194	) => {
195	try {	17✔
196	const params =
197	limit !== null && offset !== null	17✔
198	? [regionId, limit, offset]
199	: [regionId];
200	let query =
201	limit !== null && offset !== null	17✔
202	? GET_ENTRANCES_WITH_QUALITY_BY_REGION
203	: GET_ENTRANCES_WITH_QUALITY_BY_REGION.replace(
204	'LIMIT $2 OFFSET $3',
205	''
206	);
207	query = applySort(query, sort, order);	17✔
208	const queryResult = await CommonService.query(query, params);	17✔
209	return queryResult.rows;	14✔
210	} catch (e) {
211	sails.log.error(e);	3✔
212	return null;	3✔
213	}
214	},
215
216	/**
217	*
218	* @param {string} regionId ISO 3166-2 code (e.g., 'US-TN')
219	* @returns {int} count of entrances in region
220	*/
221	getEntrancesWithQualityByRegionCount: async (regionId) => {
222	try {	14✔
223	const queryResult = await CommonService.query(	14✔
224	COUNT_ENTRANCES_WITH_QUALITY_BY_REGION,
225	[regionId]
226	);
227	return parseInt(queryResult.rows[0].count, 10);	14✔
228	} catch (e) {
229	sails.log.error(e);	×
230	return 0;	×
231	}
232	},
233
234	/**
235	*
236	* @param {number} entranceId
237	* @returns {Object\|null} the materialized view row, or null if not found
238	*/
239	getEntranceQualityById: async (entranceId) => {
240	try {	5✔
241	const queryResult = await CommonService.query(	5✔
242	'SELECT * FROM v_data_quality_compute_entrance WHERE id_entrance = $1 ORDER BY id_massif NULLS LAST LIMIT 1',
243	[entranceId]
244	);
245	return queryResult.rows[0] \|\| null;	5✔
246	} catch (e) {
247	sails.log.error(e);	×
248	return null;	×
249	}
250	},
251	};

GrottoCenter / grottocenter-api / 25261295508

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous