Filter (Where)
I want to make where is optional like is user add searchParams then it will search otherwise select all data from db.
In the down I just shared my code,, please help me to fix that as soon as possible, please
const [lengths, profiles] = await Promise.all([
db.select({ count: sql<number>`count(*)` }).from(users),
db
.select()
.from(users)
.where(
or(
ilike(users.name, `%${searchParams}%`),
ilike(users.location, `%${searchParams}%`)
)
)
.limit(itemPerPage)
.offset(offset),
]);
const count = lengths[0].count;
const [lengths, profiles] = await Promise.all([
db.select({ count: sql<number>`count(*)` }).from(users),
db
.select()
.from(users)
.where(
or(
ilike(users.name, `%${searchParams}%`),
ilike(users.location, `%${searchParams}%`)
)
)
.limit(itemPerPage)
.offset(offset),
]);
const count = lengths[0].count;
4 Replies
full code is here..
currently i'm using this way,,
but I wish there are have any way to do this.
import React from "react";
import { db } from "@/database";
import { users } from "@/schemas";
import ProfileCard from "./ProfileCard";
import Paginations from "../pagination/Paginations";
import { ilike, or, sql } from "drizzle-orm";
import Notfound from "../shared/Notfound";
import Search from "../shared/search/Search";
type Props = {
page: string;
searchParams?: string;
};
const ProfileGrid = async ({ page, searchParams }: Props) => {
const currentPage = parseInt(page); // like 1
const itemPerPage = 5; // we want to show 5 item in per pages
const offset = (currentPage - 1) * itemPerPage; // (1 - 1) * 3 = 0
const [lengths, profiles] = await Promise.all([
db.select({ count: sql<number>`count(*)` }).from(users),
db
.select()
.from(users)
.where(
or(
ilike(users.name, `%${searchParams}%`),
ilike(users.location, `%${searchParams}%`)
)
)
.limit(itemPerPage)
.offset(offset),
]);
const count = lengths[0].count;
return (
<div className="mt-32 mb-8 border-t-orange-500 ml-[20rem] px-6 overflow-y-scroll">
<Search />
<div className="flex-center flex-col gap-3">
{count <= 0 ? (
<Notfound />
) : (
profiles.map((item, index: number) => (
<ProfileCard
key={index}
role={item.role}
description={item.description}
location={item.location}
name={item.name}
portfolio={item.portfolio as string}
skill={item.skills}
profileImage={item.profileImage as string}
social={item.social as any}
/>
))
)}
</div>
<Paginations
hasNextPage={currentPage < Math.ceil(count / itemPerPage)}
hasPrevPage={currentPage !== 1}
/>
</div>
);
};
export default ProfileGrid;
import React from "react";
import { db } from "@/database";
import { users } from "@/schemas";
import ProfileCard from "./ProfileCard";
import Paginations from "../pagination/Paginations";
import { ilike, or, sql } from "drizzle-orm";
import Notfound from "../shared/Notfound";
import Search from "../shared/search/Search";
type Props = {
page: string;
searchParams?: string;
};
const ProfileGrid = async ({ page, searchParams }: Props) => {
const currentPage = parseInt(page); // like 1
const itemPerPage = 5; // we want to show 5 item in per pages
const offset = (currentPage - 1) * itemPerPage; // (1 - 1) * 3 = 0
const [lengths, profiles] = await Promise.all([
db.select({ count: sql<number>`count(*)` }).from(users),
db
.select()
.from(users)
.where(
or(
ilike(users.name, `%${searchParams}%`),
ilike(users.location, `%${searchParams}%`)
)
)
.limit(itemPerPage)
.offset(offset),
]);
const count = lengths[0].count;
return (
<div className="mt-32 mb-8 border-t-orange-500 ml-[20rem] px-6 overflow-y-scroll">
<Search />
<div className="flex-center flex-col gap-3">
{count <= 0 ? (
<Notfound />
) : (
profiles.map((item, index: number) => (
<ProfileCard
key={index}
role={item.role}
description={item.description}
location={item.location}
name={item.name}
portfolio={item.portfolio as string}
skill={item.skills}
profileImage={item.profileImage as string}
social={item.social as any}
/>
))
)}
</div>
<Paginations
hasNextPage={currentPage < Math.ceil(count / itemPerPage)}
hasPrevPage={currentPage !== 1}
/>
</div>
);
};
export default ProfileGrid;
const [lengths, profiles] = await Promise.all([
db.select({ count: sql<number>`count(*)` }).from(users),
searchParams
? db
.select()
.from(users)
.where(
or(
ilike(users.name, `%${searchParams}%`),
ilike(users.location, `%${searchParams}%`)
)
)
.limit(itemPerPage)
.offset(offset)
: db.select().from(users).limit(itemPerPage).offset(offset),
]);
const [lengths, profiles] = await Promise.all([
db.select({ count: sql<number>`count(*)` }).from(users),
searchParams
? db
.select()
.from(users)
.where(
or(
ilike(users.name, `%${searchParams}%`),
ilike(users.location, `%${searchParams}%`)
)
)
.limit(itemPerPage)
.offset(offset)
: db.select().from(users).limit(itemPerPage).offset(offset),
]);
I believe the
.where method accepts undefined
const [lengths, profiles] = await Promise.all([
db.select({ count: sql<number>`count(*)` }).from(users),
db
.select()
.from(users)
.where(searchParams ?
or(
ilike(users.name, `%${searchParams}%`),
ilike(users.location, `%${searchParams}%`)
) : undefined
)
.limit(itemPerPage)
.offset(offset)
]);
const [lengths, profiles] = await Promise.all([
db.select({ count: sql<number>`count(*)` }).from(users),
db
.select()
.from(users)
.where(searchParams ?
or(
ilike(users.name, `%${searchParams}%`),
ilike(users.location, `%${searchParams}%`)
) : undefined
)
.limit(itemPerPage)
.offset(offset)
]);
@Angelelz thanks let me try with this..
another question boss 😄
can you tell me how can i also filter from array likes we have a array of fndsnames
so, i can access that array using users.fndsName
let fndsName = ["sabbir", "shuvo", "robin", "alex"];
let fndsName = ["sabbir", "shuvo", "robin", "alex"];
I don't understand the question. Do you want to filter a query based on a column having one of those values?