1

I'm newbie in a backend development and stuck with a simple request and ask for help. I am trying to get a simple join:

select * from product a, product_barcode b
 where a.productid = b.productid

Structure of the classes:

Product

class Product extends ManagedObject<product> implements product {}

class product {
  @primaryKey
  int productid;

  String rusname;
  String engname;

  ManagedSet<Product_barcode> products;
}

Product_barcode

Class Product_barcode extends ManagedObject<product_barcode>
    implements product_barcode {}

class product_barcode {
  @primaryKey
  int productid;

  String barcode;

  @Relate(#products)
  Product product;
}

Request

  @Operation.get()
  Future<Response> getProducts() async {
    final productQuery = Query<Product>(context)..join(set: (a) => a.products);
    final res = await productQuery.fetch();

    return Response.ok(res);
  }

When i make a request I am getting error:

SELECT t0.productid,t0.rusname,t0.engname,t1.productid,t1.barcode,t1.product_productid FROM product t0 LEFT OUTER JOIN product_barcode t1 ON t0.productid=t1.product_productid
column t1.product_productid does not exist

What I am doing wrong? I do not have a column product_productid in my database

Update Table structure

drugs=# \dt product_barcode
              List of relations
 Schema |      Name       | Type  |  Owner
--------+-----------------+-------+----------
 public | product_barcode | table | druguser
(1 row)


drugs=# \d product_barcode
                 Table "public.product_barcode"
  Column   |       Type        | Collation | Nullable | Default
-----------+-------------------+-----------+----------+---------
 productid | integer           |           | not null |
 barcode   | character varying |           | not null |
Indexes:
    "product_barcode_pk" PRIMARY KEY, btree (productid, barcode)

drugs=# \dt product
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | product | table | druguser
(1 row)


drugs=# \d product
                      Table "public.product"
         Column          |  Type  | Collation | Nullable | Default
-------------------------+--------+-----------+----------+---------
 productid               | bigint |           | not null |
 rusname                 | text   |           |          |
 engname                 | text   |           |          |
 registrationdate        | text   |           |          |
 dateofcloseregistration | text   |           |          |
 registrationnumber      | text   |           |          |
 composition             | text   |           |          |
 zipinfo                 | text   |           |          |
 producttypecode         | text   |           |          |
 marketstatusid          | text   |           |          |
Indexes:
    "product_pkey" PRIMARY KEY, btree (productid)
Timur
  • 111
  • 1
  • 11
  • 1
    This code is correct (other than the capitalized `Class`) and copying it into a project and running it works correctly. My guess is that you have renamed some fields after running migrations - probably `product_id` - and so your application's data model is out of sync with your database's schema. When you run `\dt product` and `\dt product_barcode` in your `psql` shell, what do you see? Also, you should always camelcase Dart classes (this doesn't matter for any errors you are experiencing, but it'll make your Dart code more readable to other Dart users). – Joe Conway Jun 08 '19 at 13:56
  • @JoeConway, thanks for your help! Check the "Update" section for current table structure. Are the migration is a mandatory process? I just created tables in PLSQL manually and filled them with data, then I defined corresponding classes in Aqeduct so I didn't use migration process – Timur Jun 08 '19 at 19:31
  • Yes, use the migration process. It will build your tables and take diffs when you change your application schemas. You can do it manually, but your field names in your classes must match your column names. – Joe Conway Jun 09 '19 at 16:28
  • @JoeConway, I did a migration process and it created a new column `product_productid`, that's fine, but I should not have such a field in my database. I just need to join data using existing columns `where a.productid = b.productid` – Timur Jun 10 '19 at 13:45
  • Are you retrofitting the ORM to an existing database? This isn't a supported use case, but if you are going down this route, you're going to have to update your database to match the naming conventions of columns Aqueduct expects. A foreign key column is named by taking the name of the relationship field and joining it with the primary key of the related table with a `_`, e.g. `product_id`. – Joe Conway Jun 11 '19 at 13:19

0 Answers0