Function playerSearch

app/player/player.service.js:3–60

(year, school, position, searchTerm)

module.exports = (db) => {

    const playerSearch = async (year, school, position, searchTerm) => {
        let filter;
        let params;
        let index;

        if (year) {
            filter = 'WHERE att.start_year <= $1 AND att.end_year >= $1';
            params = [year];
            index = 2;
        } else {
            filter = '';
            params = [];
            index = 1;
        }

        if (school) {
            filter += ` AND LOWER(t.school) = LOWER($${index})`;
            params.push(school);
            index++;
        }

        if (position) {
            filter += ` AND LOWER(p.abbreviation) = LOWER($${index})`;
            params.push(position);
            index++;
        }

        filter += ` AND LOWER(a.name) LIKE LOWER('%$${index}:value%')`;
        params.push(searchTerm);
        index++;

        const results = await db.any(`
        SELECT DISTINCT a.id, t.school, a.name, a.first_name, a.last_name, a.weight, a.height, a.jersey, p.abbreviation AS "position", h.city || ', ' || h.state AS hometown, '#' || t.color AS color, '#' || t.alt_color AS alt_color
        FROM athlete AS a
            INNER JOIN athlete_team AS att ON a.id = att.athlete_id
            INNER JOIN team AS t ON att.team_id = t.id
            INNER JOIN "position" AS p ON a.position_id = p.id
            INNER JOIN hometown AS h ON a.hometown_id = h.id
        ${filter}
        ORDER BY a.name
        LIMIT 100
        `, params);

        return results.map(r => ({
            id: r.id,
            team: r.school,
            name: r.name,
            firstName: r.first_name,
            lastName: r.last_name,
            weight: r.weight,
            height: r.height,
            jersey: r.jersey,
            position: r.position,
            hometown: r.hometown,
            teamColor: r.color,
            teamColorSecondary: r.alt_color
        }));
    };