Where clause issue

[ZainNemat]

I had an issue using the where clause with one of my tables. It was an appointment table which has a foreign key of user workspaces, the user workspace table has 2 foreign keys one of which is the user id and the other is the id of the workspace that they are in.

export const useAppointmentData = (): {
  data: Appointment[];
  isLoading: boolean;
  error: false | FetchError;
} => {
  const { currentWorkspaceId, workspaceAccessTokens, refreshAccessToken } = useAuth();
  const token = currentWorkspaceId ? workspaceAccessTokens[currentWorkspaceId] : '';

  const {
    data: userWorkspaceData,
    isLoading: userWorkspaceLoading,
    error: userWorkspaceError,
  } = useUserWorkspaceData();

  const userWorkspaceIds = useMemo(() => {
    const ids = userWorkspaceData.filter((uw) => uw.workspaceId === currentWorkspaceId).map((uw) => uw.id);

    console.log('[useAppointmentData] Filtered userWorkspaceIds:', ids);
    return ids;
  }, [userWorkspaceData, currentWorkspaceId]);

  const whereClause = useMemo(() => {
    if (!userWorkspaceIds.length) return undefined;

    const clause = `"userWorkspaceId" IN (${userWorkspaceIds.map((id) => `'${id}'`).join(', ')})`;
    console.log('[useAppointmentData] whereClause:', clause);
    return clause;
  }, [userWorkspaceIds]);

  const shape = useShape<Appointment>({
    url: `${BACKEND_URL}/electric/v1/shape`,
    headers: {
      Authorization: `Bearer ${token}`,
      cache: 'no-store',
    },
    params: {
      table: 'appointment',
      ...(whereClause ? { where: whereClause } : {}),
    },
    onError: async (error) => {
      if (error instanceof FetchError && currentWorkspaceId && error.status === 401) {
        console.warn('[useAppointmentData] Token expired. Refreshing...');
        await refreshAccessToken(currentWorkspaceId);
        return {};
      }
      throw error;
    },
  });

  return {
    data: shape.data ?? [],
    isLoading: shape.isLoading || userWorkspaceLoading,
    error: shape.error || userWorkspaceError,
  };
};

I have this code applying the where clause, so it takes an array of user workspace ids and attempts to find all the appointments associated with the associated user workspace ids. The where clause ends up looking something like this "whereClause: "userWorkspaceId" IN ('ID1', 'ID2')" It's retrieving only the appointments associated with the first user workspace id that I passed in the where clause currently. My appointment schema looks something like this, nestjs entity with typeORM.

export class Appointment {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @Column({ type: 'uuid' })
  patientId!: string;

  @ManyToOne(() => Patient, { nullable: false })
  @JoinColumn({ name: 'patientId' })
  patient!: Patient;

  @Column({ type: 'uuid' })
  userWorkspaceId!: string;

  @ManyToOne(() => UserWorkspace, { nullable: false })
  @JoinColumn({ name: 'userWorkspaceId' })
  userWorkspace!: UserWorkspace;

  @Column({ type: 'timestamp' })
  startTime!: Date;

  @Column({ type: 'timestamp' })
  endTime!: Date;

  @Column({ type: 'uuid', nullable: true })
  treatmentId?: string | null;

  @ManyToOne(() => Treatment, { nullable: true })
  @JoinColumn({ name: 'treatmentId' })
  treatment?: Treatment | null;

  @Column({ type: 'text', nullable: true })
  quickNotes?: string | null;

  @CreateDateColumn({ type: 'timestamp' })
  createdAt!: Date;
}

And this is the user workspace relation entity

@Entity('user_workspace')
@Unique(['userId', 'workspaceId'])
export class UserWorkspace {
  @PrimaryGeneratedColumn('uuid')
  id!: string;

  @ManyToOne(() => User, (user) => user.userWorkspaces, { onDelete: 'RESTRICT', onUpdate: 'RESTRICT', nullable: false })
  @JoinColumn({ name: 'userId' })
  userId!: string;

  @ManyToOne(() => Workspace, (workspace) => workspace.userWorkspaces, {
    onDelete: 'RESTRICT',
    onUpdate: 'RESTRICT',
    nullable: false,
  })
  @JoinColumn({ name: 'workspaceId' })
  workspaceId!: string;

  @ManyToOne(() => Roles, (role) => role.id, { onDelete: 'RESTRICT', onUpdate: 'RESTRICT', nullable: false })
  role?: Roles;

  @Column()
  joinedAt?: Date = new Date();
}

And this is the electric logic which is handling the where clause if the table is the appointment table which im trying to filter in this case

import { Injectable, UnauthorizedException } from '@nestjs/common';
import { Request as ExpressRequest, Response as ExpressResponse } from 'express';
import { JwtPayload } from '../auth/jwt.strategy';

@Injectable()
export class ElectricService {
  async proxyShapeRequest(req: ExpressRequest & { user: JwtPayload }, res: ExpressResponse): Promise<void> {
    const workspaceId = req.user.workspaceId;
    if (!workspaceId) {
      throw new UnauthorizedException('Workspace ID missing in session token');
    }

    const electricUrl = new URL('http://localhost:5133/v1/shape');

    ['table', 'offset', 'cursor', 'live', 'handle', 'where'].forEach((key) => {
      if (req.query[key]) {
        electricUrl.searchParams.set(key, req.query[key] as string);
      }
    });

    const hasWhereClause = electricUrl.searchParams.has('where');
    const table = electricUrl.searchParams.get('table');

    if (!hasWhereClause && table !== 'appointment') {
      electricUrl.searchParams.set('where', `"workspaceId" = '${workspaceId}'`);
    }

    let upstreamResponse = await fetch(electricUrl.toString());
    console.log('[proxyShapeRequest] Final Electric URL:', electricUrl.toString());

    if (upstreamResponse.headers.get('content-encoding')) {
      const headers = new Headers(upstreamResponse.headers);
      headers.delete('content-encoding');
      headers.delete('content-length');

      upstreamResponse = new Response(upstreamResponse.body, {
        status: upstreamResponse.status,
        statusText: upstreamResponse.statusText,
        headers,
      });
    }

    res.status(upstreamResponse.status);
    console.log('[proxyShapeRequest] Upstream Response Headers:');
    upstreamResponse.headers.forEach((value, key) => {
      console.log(`  ${key}: ${value}`);
    });

    const body = await upstreamResponse.arrayBuffer();
    res.send(Buffer.from(body));
  }
}

With this example, it returns one appointment object even though in my database there are 2 appointment objects that it should be returning.