我在 SvelteKit 应用中使用 better-sqlite-3。我有两个表，一个用于客户，一个用于收藏，用户可以将客户设置为收藏，信息保存在 app_users_favorites 中，其中
我在 SvelteKit 应用中使用 better-sqlite-3。我有两个表，一个用于客户，一个用于收藏，用户可以将客户设置为收藏，信息保存在 中 app_users_favorites ，其中 favorite_id 对应于客户表的键 id（称为“chiave”）列。
app_users_favorites (
database_id TEXT,
user_id TEXT,
favorite_id TEXT,
);
这是客户语句，用于无限滚动页面。有一列称为 preferenceito（收藏夹），该列为空，可通过查看收藏夹表来更改，如果 chiave（键）id 在相应的行中，则将 preferenceito 设置为 1 或 0。截至目前，它可以工作，但排序不起作用。如果按 preferenceito 排序，我希望所有收藏夹客户都根据 ASC 或 DESC 出现在列表的顶部或底部。但是，对 favorite 的排序不起作用，对其他每个值的排序都起作用，所以我认为这一定是我对 preferenceito 字段的操作方式。
getClifor_gen_paginated_search: (
database_id,
tipo_clifor,
searchQuery,
limit,
offset,
orderBy,
sortDirection,
user_id
) => {
const validColumns = [
"rag_soc",
"cod_clifor",
"bloccato",
"preferito",
"categoria",
"macrocategoria",
"sottocategoria",
"citta",
"data_ult_ordine",
"data_ult_doc_ft",
"data_ult_doc_no_ft",
"des_raggr1",
"des_raggr2",
"des_raggr3",
];
const validDirections = ["ASC", "DESC"];
// Validate orderBy and sortDirection
if (!validColumns.includes(orderBy)) {
throw new Error(`Invalid column name: ${orderBy}`);
}
if (!validDirections.includes(sortDirection)) {
throw new Error(`Invalid sort direction: ${sortDirection}`);
}
// Construct the query
let orderByClause = "";
if (orderBy === "preferito") {
orderByClause = `
ORDER BY preferito ${sortDirection}, rag_soc ASC
`;
} else if (orderBy === "bloccato") {
orderByClause = `
ORDER BY bloccato ${sortDirection}, preferito DESC, rag_soc ASC
`;
} else if (orderBy === "cod_clifor") {
const reversedSortDirection = sortDirection === "ASC" ? "DESC" : "ASC";
orderByClause = `
ORDER BY CAST(cod_clifor AS SIGNED) ${reversedSortDirection}, preferito DESC
`;
} else if (orderBy === "rag_soc") {
const reversedSortDirection = sortDirection === "ASC" ? "DESC" : "ASC";
orderByClause = `
ORDER BY rag_soc ${reversedSortDirection}, preferito DESC
`;
} else if (
orderBy === "data_ult_ordine" ||
orderBy === "data_ult_doc_ft" ||
orderBy === "data_ult_doc_no_ft"
) {
orderByClause = `
ORDER BY
SUBSTR(${orderBy}, 5, 4) || '-' || SUBSTR(${orderBy}, 3, 2) || '-' || SUBSTR(${orderBy}, 1, 2) ${sortDirection},
rag_soc ASC
`;
}
const query = `
SELECT
clifor_gen.*,
CASE
WHEN app_users_favorites.favorite_id IS NOT NULL THEN 1
ELSE 0
END AS preferito
FROM
clifor_gen
LEFT JOIN
app_users_favorites
ON clifor_gen.database_id = app_users_favorites.database_id
AND app_users_favorites.user_id = ?
AND app_users_favorites.favorite_id = clifor_gen.chiave
WHERE
clifor_gen.database_id = ?
AND clifor_gen.tipo_clifor = ?
AND (
clifor_gen.rag_soc LIKE ?
OR clifor_gen.cod_clifor LIKE ?
OR clifor_gen.categoria LIKE ?
OR clifor_gen.macrocategoria LIKE ?
OR clifor_gen.sottocategoria LIKE ?
OR clifor_gen.citta LIKE ?
OR clifor_gen.des_raggr1 LIKE ?
OR clifor_gen.des_raggr2 LIKE ?
OR clifor_gen.des_raggr3 LIKE ?
)
${orderByClause}
LIMIT ? OFFSET ?
`;
const stmt = db.prepare(query);
return stmt.all(
user_id, // user_id for checking if the item is favorited
database_id,
tipo_clifor,
`%${searchQuery}%`,
`%${searchQuery}%`,
`%${searchQuery}%`,
`%${searchQuery}%`,
`%${searchQuery}%`,
`%${searchQuery}%`,
`%${searchQuery}%`,
`%${searchQuery}%`,
`%${searchQuery}%`,
limit,
offset
);
},
};
