PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

[KacperMekarski]

Rails v6.0.4.6 Ruby v2.5.3

Issue

This statement is invalid with SELECT DISTINCT clause:

PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ...dly_idslugs"."slug" = 'product-3-35555' ORDER BY "friendly...

Solution

Add friendly_id_slugs.id to select statement:

def slug_table_record(id)
  select(quoted_table_name + '.*', Slug.arel_table[:id]).joins(:slugs).where(slug_history_clause(id)).order(Slug.arel_table[:id].desc).first
end

[brunnossanttos]

Did you find the solution?

[petergoldstein]

Does anyone have a reproducible test case that we can add to the specs?

[brunnossanttos]

I don´t have specs, but I will try explain.

I have a repository method for return promotions paginated and ordered for a logical field(qtdViews)

public async viewsOrdenation(
    { order_by, logged_user_id },
    { page, skip, take }: SearchParams,
  ): Promise<IPaginatorPromotion> {
    const promotions = await this.ormRepository
      .createQueryBuilder('promotions')
      .loadRelationCountAndMap('promotions.qtdComments', 'promotions.comments')
      .loadRelationCountAndMap('promotions.qtdViews', 'promotions.views')
      .loadRelationCountAndMap(
        'promotions.qtdLikes',
        'promotions.likes',
        'il',
        qb => qb.where('il.is_liked = :is_liked', { is_liked: true }),
      )
      .loadRelationCountAndMap(
        'promotions.is_liked',
        'promotions.likes',
        'iL',
        qb =>
          qb
            .where('iL.profile_id = :profile_id', {
              profile_id: logged_user_id,
            })
            .andWhere('iL.is_liked = :is_liked', { is_liked: true }),
      )
      .leftJoinAndSelect('promotions.advertiser', 'advertiser')
      .leftJoin('advertiser.profile', 'profile')
      .addSelect([
        'profile.id',
        'profile.name',
        'profile.email',
        'profile.cellphone',
        'profile.avatar',
      ])
      .leftJoin('promotions.views', 'views')
      .addSelect('COUNT(views.id) as qtdViews')
      .where('promotions.is_expired = :is_expired', { is_expired: false })
      .orderBy('qtdViews', 'DESC')
      .groupBy('promotions.id, profile.id, advertiser.id')
      .skip(skip)
      .take(take)
      .getMany();

    const count = await this.ormRepository
      .createQueryBuilder('promotions')
      .where('promotions.is_expired = :is_expired', { is_expired: false })
      .getCount();

    const data = {
      per_page: take,
      total: count,
      cuurent_page: page,
      data: promotions,
    };

    return data;
  }

    return data;
  }

But when the route it´s called return the error:

"QueryFailedError: for SELECT DISTINCT, ORDER BY expressions must appear in select list"

Can you help me, please?

[parndt]

@brunnossanttos that's all TypeScript code you posted above, so I'm not quite sure how that relates to this issue 😄

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[parndt]

Add friendly_id_slugs.id to select statement:

won't this conflict with the main table's id column?