cmck
cmck
PostsComments
XXata
Created by Panicintrinsica on 10/16/2024 in #help
"Projection/Select" in search queries?
At the moment, there is no way to limit the number of columns returned in a record when searching. The whole record(s) is returned. You can filter before the search which should help limit the amount of data returned.
2 replies
XXata
Created by almac2017 on 10/15/2024 in #help
Links (Relationships) database structure and filter / query approach
A json column is a good place to start. You could just have orgs and projects and put all the project information (milestones, tasks, subtasks) in a json column. If you really need to query any of the subtask, task, or milestone tables by themselves or with joins (tasks & subtasks, for example), then having a table for them makes sense. Otherwise, it's probably wiser to keep the info in one place. You might also consider a nosql database if you really need a deep hierarchical structure of data.
5 replies
XXata
Created by almac2017 on 10/15/2024 in #help
Links (Relationships) database structure and filter / query approach
And the query: 
SELECT 
    o."org_name",
    p."project_name",
    ARRAY_AGG(
        DISTINCT m."milestone_name"
    ) AS milestones,
    ARRAY_AGG(
        DISTINCT t."task_name"
    ) AS tasks,
    ARRAY_AGG(
        DISTINCT s."subtask_name"
    ) AS subtasks
FROM 
    "organisations" o
JOIN 
    "projects" p ON p."org_xata_id" = o."xata_id"
LEFT JOIN 
    "milestones" m ON m."project_xata_id" = p."xata_id"
LEFT JOIN 
    "tasks" t ON t."milestone_xata_id" = m."xata_id"
LEFT JOIN 
    "subtasks" s ON s."parent_task_xata_id" = t."xata_id"
WHERE 
    o."xata_id" = 'xata_2'  -- Replace 'xata_1' with the specific org_xata_id
GROUP BY 
    o."org_name", p."project_name"
ORDER BY 
    p."project_name";
SELECT 
    o."org_name",
    p."project_name",
    ARRAY_AGG(
        DISTINCT m."milestone_name"
    ) AS milestones,
    ARRAY_AGG(
        DISTINCT t."task_name"
    ) AS tasks,
    ARRAY_AGG(
        DISTINCT s."subtask_name"
    ) AS subtasks
FROM 
    "organisations" o
JOIN 
    "projects" p ON p."org_xata_id" = o."xata_id"
LEFT JOIN 
    "milestones" m ON m."project_xata_id" = p."xata_id"
LEFT JOIN 
    "tasks" t ON t."milestone_xata_id" = m."xata_id"
LEFT JOIN 
    "subtasks" s ON s."parent_task_xata_id" = t."xata_id"
WHERE 
    o."xata_id" = 'xata_2'  -- Replace 'xata_1' with the specific org_xata_id
GROUP BY 
    o."org_name", p."project_name"
ORDER BY 
    p."project_name";
5 replies
XXata
Created by almac2017 on 10/15/2024 in #help
Links (Relationships) database structure and filter / query approach
Q: Is the above overkill or am on the right path? A: It's best to avoid lots of nested hierarchical relations, so I would try to reduce the number of one-to-many relations if possible. Performance will be impacted as you'll have to perform a lot of joins over many rows to get all the data you want. If you don't have many orgs, projects, etc. then this mightn't be an issue, but even 3 orgs with 3 projects, 3 milestones, 5 tasks, 3 subtasks each produces 54675 subtasks. Q: You can test your schema and queries using xata itself (or a local pg installation with pg admin). Make sure your tables have primary keys. Below is a sample schema in xata and a query that aggregates all the data by organisation. Sample schema (be sure to click on "Schema & migration" after creation to adapt it and add the xata columns) 
CREATE TABLE "organisations" (
  "org_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "org_name" text
);

CREATE TABLE "projects" (
  "projects_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "project_name" text,
  "org_xata_id" text
);

CREATE TABLE "milestones" (
  "milestones_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "milestone_name" text,
  "project_xata_id" text
);

CREATE TABLE "tasks" (
  "tasks_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "task_name" text,
  "milestone_xata_id" text
);

CREATE TABLE "subtasks" (
  "subtasks_id" integer PRIMARY key,
  "xata_id" text UNIQUE NOT NULL,
  "subtask_name" text,
  "parent_task_xata_id" text
);

ALTER TABLE "projects" ADD FOREIGN KEY ("org_xata_id") REFERENCES "organisations" ("xata_id");

ALTER TABLE "milestones" ADD FOREIGN KEY ("project_xata_id") REFERENCES "projects" ("xata_id");

ALTER TABLE "tasks" ADD FOREIGN KEY ("milestone_xata_id") REFERENCES "milestones" ("xata_id");

ALTER TABLE "subtasks" ADD FOREIGN KEY ("parent_task_xata_id") REFERENCES "tasks" ("xata_id");
CREATE TABLE "organisations" (
  "org_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "org_name" text
);

CREATE TABLE "projects" (
  "projects_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "project_name" text,
  "org_xata_id" text
);

CREATE TABLE "milestones" (
  "milestones_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "milestone_name" text,
  "project_xata_id" text
);

CREATE TABLE "tasks" (
  "tasks_id" integer PRIMARY KEY,
  "xata_id" text UNIQUE NOT NULL,
  "task_name" text,
  "milestone_xata_id" text
);

CREATE TABLE "subtasks" (
  "subtasks_id" integer PRIMARY key,
  "xata_id" text UNIQUE NOT NULL,
  "subtask_name" text,
  "parent_task_xata_id" text
);

ALTER TABLE "projects" ADD FOREIGN KEY ("org_xata_id") REFERENCES "organisations" ("xata_id");

ALTER TABLE "milestones" ADD FOREIGN KEY ("project_xata_id") REFERENCES "projects" ("xata_id");

ALTER TABLE "tasks" ADD FOREIGN KEY ("milestone_xata_id") REFERENCES "milestones" ("xata_id");

ALTER TABLE "subtasks" ADD FOREIGN KEY ("parent_task_xata_id") REFERENCES "tasks" ("xata_id");
5 replies
XXata
Created by Will Lutz on 10/14/2024 in #help
REST API - Unknown Column Type
No description
4 replies
XXata
Created by Will Lutz on 10/14/2024 in #help
REST API - Unknown Column Type
Can you share the function you are using when filtering? I'll try to recreate the error. In general, you should be possible to filter by an enum column.
4 replies
XXata
Created by Helim on 10/2/2024 in #help
Column is not an object or link
Hi Helim, thanks for pointing this out. We're working on fixing the view for the child table. As a workaround, you should be able to reference the xata_id field in the child table which will allow the child table to render in the UI.
3 replies
XXata
Created by Cawfehhh on 10/1/2024 in #help
High severity vulnerabilities when installing @xata.io/cli
Thanks for pointing this out. The vulnerabilities mentioned for lodash are false positives as we don't use the flagged code paths. They can be ignored safely. We're deploying a fix so the false positives won't appear during future installs.
3 replies
XXata
Created by johncmacy on 9/25/2024 in #help
Error: Option apiKey is required in Remix loader
So, I was able to query in a remix loader without passing my api key and after following this tutorial . You could try following this guide from scratch and see if you can get it to work without adding the apiKey etc. 
export const meta: MetaFunction = () => {
  return [
    { title: 'Xata and Remix' },
    { name: "description", content: "Welcome to Xata!" },
  ];
};

export async function loader() {
  const xata = getXataClient();
  const posts = await xata.db.Posts.getAll();
  return json({
    posts
  });
}
export const meta: MetaFunction = () => {
  return [
    { title: 'Xata and Remix' },
    { name: "description", content: "Welcome to Xata!" },
  ];
};

export async function loader() {
  const xata = getXataClient();
  const posts = await xata.db.Posts.getAll();
  return json({
    posts
  });
}
I'd also recommend running the xata status and xata auth status commands in your project directory to check if the api key can be loaded.
4 replies
XXata
Created by johncmacy on 9/25/2024 in #help
Error: Option apiKey is required in Remix loader
Hi there, thanks for reaching out, let me take a look and see if there's a better solution.
4 replies