Press n or j to go to the next uncovered block, b, p or k for the previous block.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 | 20x 20x 20x 4x 4x 4x 4x 4x 20x 23x 12x 12x 12x 12x 12x 12x 12x 12x 6x 1x 1x 1x 5x 4x 4x 12x 12x 12x 20x | /**
* Services for query functions.
* @packageDocumentation
*/
import { BaseService } from "./util";
/**
* Query parameters.
*/
export interface QueryParams {
search?: string;
programIDs?: number[];
locationTypeIDs?: number[];
statusIDs?: number[];
ratings?: number[];
}
/**
* Query sort options.
*/
export type QuerySortOptions =
| "program"
| "locationType"
| "userStatus"
| "rating"
| "timestamp"
| "city"
| "country";
/**
* Query post architecture.
*/
export interface QueryPost {
id: string;
location: string;
rating: number;
}
/**
* Query services.
*/
export class QueryService extends BaseService {
/**
* Perform a basic search for posts.
*
* @param search Search parameters.
* @returns All posts that match the search parameters.
*/
public async query(search: string): Promise<QueryPost[]> {
const searchLike = "%" + search + "%";
const sql = `
SELECT
Post.id AS id,
Post.location AS location,
Rating.general AS rating
FROM Post
JOIN Program ON Post.programID = Program.id
JOIN Rating ON Post.ratingID = Rating.id
WHERE
Post.approved = TRUE
AND (
LOWER(Post.content) LIKE LOWER(?)
OR LOWER(Post.location) LIKE LOWER(?)
OR LOWER(Post.city) LIKE LOWER(?)
OR LOWER(Post.country) LIKE LOWER(?)
OR LOWER(Program.name) LIKE LOWER(?)
)
ORDER BY Post.createTime DESC;
`;
const params = Array(5).fill(searchLike);
const rows: QueryPost[] = await this.dbm.execute(sql, params);
return rows;
}
/**
* Perform an advanced search for posts.
*
* @param parameters Search parameters.
* @param sortBy The element to sort by.
* @param sortAscending Whether to sort ascending or descending.
* @returns All posts that match the search parameters.
*/
public async advancedQuery(
parameters: QueryParams,
sortBy: QuerySortOptions,
sortAscending: boolean = true
): Promise<QueryPost[]> {
const whereOptions = {
programIDs: "Post.programID",
locationTypeIDs: "Post.locationTypeID",
statusIDs: "User.statusID",
ratings: "Rating.general",
};
const sortOptions = {
program: "Program.name",
locationType: "LocationType.name",
userStatus: "UserStatus.name",
rating: "Rating.general",
timestamp: "Post.createTime",
city: "Post.city",
country: "Post.country",
};
const sortOrder = sortAscending ? "ASC" : "DESC";
// SELECT
// Post.id AS id,
// Post.location AS location,
// Rating.general AS rating
// FROM Post
// JOIN User ON Post.userID = User.id
// JOIN LocationType ON Post.locationTypeID = LocationType.id
// JOIN UserStatus ON User.statusID = UserStatus.id
// JOIN Program ON Post.programID = Program.id
// JOIN Rating ON Post.ratingID = Rating.id
// WHERE
// Post.approved = TRUE
// AND (
// LOWER(Post.content) LIKE LOWER(%?%)
// OR LOWER(Post.location) LIKE LOWER(%?%)
// OR LOWER(Post.city) LIKE LOWER(%?%)
// OR LOWER(Post.country) LIKE LOWER(%?%)
// OR LOWER(Program.name) LIKE LOWER(%?%)
// )
// AND Post.programID IN (...)
// AND Post.locationTypeID IN (...)
// AND User.statusID IN (...)
// AND Rating.general IN (...)
// ORDER BY ${sortOptions[sortBy]} ${sortOrder};
const sqlStart = `
SELECT
Post.id AS id,
Post.location AS location,
Rating.general AS rating
FROM Post
JOIN User ON Post.userID = User.id
JOIN LocationType ON Post.locationTypeID = LocationType.id
JOIN UserStatus ON User.statusID = UserStatus.id
JOIN Program ON Post.programID = Program.id
JOIN Rating ON Post.ratingID = Rating.id
`;
const sqlEnd = `ORDER BY ${sortOptions[sortBy]} ${sortOrder};`;
let params = [];
let whereClause = ["Post.approved = TRUE"];
for (const parameter in parameters) {
if (parameter === "search") {
const searchLike = "%" + parameters[parameter] + "%";
whereClause.push(`(
LOWER(Post.content) LIKE LOWER(?)
OR LOWER(Post.location) LIKE LOWER(?)
OR LOWER(Post.city) LIKE LOWER(?)
OR LOWER(Post.country) LIKE LOWER(?)
OR LOWER(Program.name) LIKE LOWER(?)
)`);
params.push(...Array(5).fill(searchLike));
} else if (parameters[parameter].length > 0) {
whereClause.push(`${whereOptions[parameter]} IN (?)`);
params.push(parameters[parameter]);
}
}
const sql = `${sqlStart} WHERE ${whereClause.join(" AND ")} ${sqlEnd}`;
const rows: QueryPost[] = await this.dbm.execute(sql, params);
return rows;
}
}
|