Moor: Calling join on a select statement resets where and order-by clauses

[raphmte]

Good afternoon, how are you?

I am trying to make a query with Join, however it is not only filtering the files I want:
`final placeLoading = alias(places, 'placeLoading');
final cityLoading = alias(locationCities, 'cityLoading');

final placeDischarge = alias(places, 'placeDischarge');
final cityDischarge = alias(locationCities, 'cityDischarge');

final query = await (select(serviceOrders)
  ..where((s) => s.sorCode.equals(sorCode)))
    .join([
  leftOuterJoin(serviceOrdersStatus,
      serviceOrdersStatus.sstStatus.equalsExp(serviceOrders.sorStatus)),
  leftOuterJoin(placeLoading,
      placeLoading.plaCode.equalsExp(serviceOrders.plaCodeLoading)),
  leftOuterJoin(
      cityLoading, cityLoading.citCode.equalsExp(placeLoading.citCode)),
  leftOuterJoin(placeDischarge,
      placeDischarge.plaCode.equalsExp(serviceOrders.plaCodeDischarge)),
  leftOuterJoin(cityDischarge,
      cityDischarge.citCode.equalsExp(placeDischarge.citCode)),
]).get();

List<ServiceOrderWithDetails> finalList = query.map((resultRow) {
  return ServiceOrderWithDetails(
    resultRow.readTable(serviceOrders),
    resultRow.readTable(serviceOrdersStatus),
    resultRow.readTable(placeLoading),
    resultRow.readTable(cityLoading),
    resultRow.readTable(placeDischarge),
    resultRow.readTable(cityDischarge),
  );
}).toList();`

This returns me 3 results and should only return 1. So if I test without the Join:
final query = await (select(serviceOrders) ..where((s) => s.sorCode.equals(sorCode))).get();

It returns me exactly the only result.

Am I doing something wrong?

Taking advantage of the topic, what is the best way for me to get a single record in the table? When for example I want to get the record for the primary key that does not repeat.

[simolus3]

When you call join _after_ calling where or orderBy, these get reset, so you first need to use join and apply filters afterwards. There's no a good reason for that, so I'll consider this a bug. Nevertheless, you can "fix" your query with:

final query = await (select(serviceOrders)
    ..join([
  leftOuterJoin(serviceOrdersStatus,
      serviceOrdersStatus.sstStatus.equalsExp(serviceOrders.sorStatus)),
  leftOuterJoin(placeLoading,
      placeLoading.plaCode.equalsExp(serviceOrders.plaCodeLoading)),
  leftOuterJoin(
      cityLoading, cityLoading.citCode.equalsExp(placeLoading.citCode)),
  leftOuterJoin(placeDischarge,
      placeDischarge.plaCode.equalsExp(serviceOrders.plaCodeDischarge)),
  leftOuterJoin(cityDischarge,
      cityDischarge.citCode.equalsExp(placeDischarge.citCode)),
  ]
    ..where(serviceOrders.sorCode.equals(sorCode)))
  ).get();

[simolus3]

Now fixed on develop

[raphmte]

Hello @simolus3 , a tried your code, but:

This error appears on Where stantement.

[simolus3]

Ah sorry, stupid mistake I made above - you need to call .where on the select statement, not on the list. So line 159 in your code should probably be ]), like this:

final query = await (select(serviceOrders)
    ..join([
  leftOuterJoin(serviceOrdersStatus,
      serviceOrdersStatus.sstStatus.equalsExp(serviceOrders.sorStatus)),
  // ...
  ])
    ..where(serviceOrders.sorCode.equals(sorCode))
  ).get();

[raphmte]

@simolus3 Hello again man, pls help in this Where instantement pls, I tried your last response but now I get an error:

Stream<List<ServiceOrderExpenseWithCatyegory>> getExpensesStream(
      {int sorCode}) {

    final query = (select(serviceOrdersExpenses)
      ..join([
        leftOuterJoin(
            serviceOrdersExpenses,
            serviceOrdersExpenses.etyCode.equalsExp(serviceOrdersExpenses.etyCode)),
      ])
      ..where((expense) => expense.sorCode.equals(sorCode))
      );

    return query.watch().map((rows) {
      return rows.map((row) {
        return ServiceOrderExpenseWithCatyegory(
            row.readTable(serviceOrdersExpenses), row.readTable(expensesTypes));
      }).toList();
    });
  }

The error:

If I try the Join with one point the error turn to Where:

final query = (select(serviceOrdersExpenses)
      .join([
        leftOuterJoin(
            serviceOrdersExpenses,
            serviceOrdersExpenses.etyCode.equalsExp(serviceOrdersExpenses.etyCode)),
      ])
      ..where((expense) => expense.sorCode.equals(sorCode))
      );

    return query.watch().map((rows) {
      return rows.map((row) {
        return ServiceOrderExpenseWithCatyegory(
            row.readTable(serviceOrdersExpenses), row.readTable(expensesTypes));
      }).toList();
    });

Error:

How I solve this? =/

[simolus3]

Hopefully this one works :grimacing:

Stream<List<ServiceOrderExpenseWithCatyegory>> getExpensesStream(
      {int sorCode}) {

    final query = select(serviceOrdersExpenses)
      .join([
        leftOuterJoin(
            serviceOrdersExpenses,
            serviceOrdersExpenses.etyCode.equalsExp(serviceOrdersExpenses.etyCode)),
      ])
      ..where(serviceOrderExpenses.sorCode.equals(sorCode));

    return query.watch().map((rows) {
      return rows.map((row) {
        return ServiceOrderExpenseWithCatyegory(
            row.readTable(serviceOrdersExpenses), row.readTable(expensesTypes));
      }).toList();
    });
  }

If you use the .. operator before the join, the joined select statement will be created but not used, it would still be the regular select statement - sorry. It's okay to use it before the where because at that point we already have the joined select statement.

[raphmte]

Well this one did not generate error in the code, but ended up generating a SQL error:
Unhandled Exception: DatabaseException(ambiguous column name: service_orders_expenses.sexCode (code 1 SQLITE_ERROR)

Let's do the following, as you commented in the previous answers that have corrected this problem for the next versions, for now I'm giving that developer way here to get going with my app, and so you update I go back and review these things. = D

Thank you very much for the help and I will let you work.

[simolus3]

Sorry that you keep getting these problems :/ I think I know where that one is coming from as well, but I don't want to make false promises. Can you give me the definition for the "ServiceOrderExpenses" table? Then I can debug that query and be certain that it works when I have it figured out.

Btw, you run a select on serviceOrdersExpenses and then join serviceOrdersExpenses (the same table). Later, you want to read both serviceOrdersExpenses and expensesTypes (a table which doesn't appear in the select statement). Is this a typo?

[raphmte]

I really wish you did not feel sorry for the problems, you have done a work of art with Moor and the only thing I have to do is thank you and report the problems I encounter to make it better every day.

And also thank you for seeing this bizarre typing error, your last code worked perfectly and the SQL error was that the table was wrong even:

  Stream<List<ServiceOrderExpenseWithCatyegory>> getExpensesStream(
      {int sorCode}) {

    final query = select(serviceOrdersExpenses)
        .join([
      leftOuterJoin(
          expensesTypes,
          expensesTypes.etyCode.equalsExp(serviceOrdersExpenses.etyCode)),
    ])
      ..where(serviceOrdersExpenses.sorCode.equals(sorCode));

    return query.watch().map((rows) {
      return rows.map((row) {
        return ServiceOrderExpenseWithCatyegory(
            row.readTable(serviceOrdersExpenses), row.readTable(expensesTypes));
      }).toList();
    });
  }

All filters have been performed correctly and now I just get the records I need, I will work on the other tables now to improve the code also with these new changes. Thank you very much again.

[raphmte]

Simo, me again. Sorry for the inconvenience, but how do I do the same where with Get?

final query = await (select(serviceOrders)
          ..where((s) => s.sorCode.equals(sorCode)))
        .join([
      leftOuterJoin(serviceOrdersStatus,
          serviceOrdersStatus.sstStatus.equalsExp(serviceOrders.sorStatus)),
      leftOuterJoin(placeLoading,
          placeLoading.plaCode.equalsExp(serviceOrders.plaCodeLoading)),
      leftOuterJoin(
          cityLoading, cityLoading.citCode.equalsExp(placeLoading.citCode)),
      leftOuterJoin(placeDischarge,
          placeDischarge.plaCode.equalsExp(serviceOrders.plaCodeDischarge)),
      leftOuterJoin(cityDischarge,
          cityDischarge.citCode.equalsExp(placeDischarge.citCode)),
    ]).get();

    List<ServiceOrderWithDetails> finalList = query.map((resultRow) {
      return ServiceOrderWithDetails(
        resultRow.readTable(serviceOrders),
        resultRow.readTable(serviceOrdersStatus),
        resultRow.readTable(placeLoading),
        resultRow.readTable(cityLoading),
        resultRow.readTable(placeDischarge),
        resultRow.readTable(cityDischarge),
      );
    }).toList();

[simolus3]

Your query should work on develop. If you're using the latest release version, you can again work around the bug with

final query = await (
  select(serviceOrders)
 .join([
    leftOuterJoin(serviceOrdersStatus,
        serviceOrdersStatus.sstStatus.equalsExp(serviceOrders.sorStatus)),
    leftOuterJoin(placeLoading,
        placeLoading.plaCode.equalsExp(serviceOrders.plaCodeLoading)),
    leftOuterJoin(
        cityLoading, cityLoading.citCode.equalsExp(placeLoading.citCode)),
    leftOuterJoin(placeDischarge,
        placeDischarge.plaCode.equalsExp(serviceOrders.plaCodeDischarge)),
    leftOuterJoin(cityDischarge,
        cityDischarge.citCode.equalsExp(placeDischarge.citCode)),
 ])
 ..where(serviceOrders.sorCode.equals(sorCode))
).get();

[raphmte]

Very good, worked perfectly. Thank you.
How do I use the dev version?

[simolus3]

Put this in your pubspec.yaml:

dependency_overrides:
  moor_flutter:
    git:
      url: https://github.com/simolus3/moor.git
      ref: develop
      path: moor_flutter/
  moor_generator:
    git:
      url: https://github.com/simolus3/moor.git
      ref: develop
      path: moor_generator/

Be aware that these development releases can be very unstable at times. I might set up a more stable prerelease branch after the next release :thinking: