PGSync version: latest
Postgres version: 14
Elasticsearch/OpenSearch version: 2.17.1
Redis version: 7.4.0
Python version: 3.10.12
why sql generate condition WHERE sc_order_item_combo_1.order_item_id = sc_order_item_1.package_id . expected: WHERE sc_order_item_combo_1.order_item_id = sc_order_item_1.id
Thanks
Schema
`[
{
"database": "order_connector",
"index": "oms_packages",
"nodes": {
"table": "sc_logistics_packages",
"primary_key": ["id"],
"schema": "public",
"columns": [
"id",
"connector_channel_code",
"store_id",
"sme_id",
"package_number",
"logistics_status",
"order_id",
"sc_warehouse_id",
"sme_warehouse_id",
"shipping_carrier",
"tracking_number",
"created_at",
"updated_at",
"print_status",
"pack_status",
"pack_abnormal",
"create_doc_status",
"warehouse_error_code",
"warehouse_error_message",
"connector_channel_error",
"order_at",
"s3_document",
"ref_warehouse_id",
"wh_export_code",
"wh_import_code",
"warehouse_code",
"package_weight",
"package_length",
"package_width",
"package_height",
"logistics_system",
"provider_or_id",
"provider_or_status",
"provider_or_error",
"provider_last_synced_at",
"fulfillment_provider_wh_code",
"ref_order_id",
"shipment_label_status",
"provider_payload",
"provider_shipment_status",
"system_package_number",
"logistic_rts_status",
"is_partial_cancelled",
"is_partial_cancelled_processed",
"pack_no"
],
"children": [
{
"table": "sc_order",
"primary_key": ["id"],
"columns": [
"id",
"store_id",
"sme_id",
"shipped_at",
"connector_channel_code",
"ref_id",
"ref_store_id",
"ref_number",
"currency_code",
"is_paid",
"paid_price",
"original_price",
"platform_service_fee",
"seller_transaction_fee",
"promotion_seller_amount",
"promotion_platform_amount",
"shipping_original_fee",
"paid_shipping_fee",
"shipping_discount_seller_fee",
"shipping_discount_platform_fee",
"buyer_transaction_fee",
"seller_discount_amount",
"platform_discount",
"shipping_fee_discount_from_3pl",
"platform_commission_fee",
"seller_coin_cash_back",
"ship_expired_at",
"other_fee",
"cancel_by",
"cancel_reason",
"buyer_cancel_reason",
"paid_at",
"order_at",
"update_time",
"payment_method",
"message_to_seller",
"note",
"customer_id",
"status",
"platform_status",
"recipient_address_id",
"created_at",
"updated_at",
"total_discount",
"return_at",
"cancel_at",
"load_status",
"sync_error",
"tts_expired",
"p_delivery_method",
"p_time_id",
"p_address_id",
"platform_status_text",
"logistic_fail",
"return_process_status",
"returned_time",
"customer_username",
"seller_revenue",
"source",
"is_connected",
"received_at",
"completed_at",
"sme_note",
"payment_transaction_code",
"person_in_charge",
"last_wh_exported_at"
],
"relationship": {
"variant": "object",
"type": "one_to_one",
"foreign_key": {
"parent": ["order_id"],
"child": ["id"]
}
}
},
{
"table": "sc_order_item",
"primary_key": ["id"],
"columns": [
"id",
"store_id",
"sme_id",
"ref_order_id",
"ref_id",
"list_ref_id",
"order_id",
"connector_channel_code",
"ref_product_id",
"sc_product_id",
"ref_variant_id",
"sc_variant_id",
"product_name",
"variant_name",
"variant_image",
"variant_sku",
"quantity_purchased",
"order_at",
"original_price",
"paid_price",
"discounted_price",
"reason",
"reason_detail",
"created_at",
"updated_at",
"sme_product_id",
"sme_variant_id",
"warehouse_error_code",
"warehouse_error_message",
"warehouse_step",
"warehouse_last_action",
"package_id",
"is_combo",
"voucher_seller_amount",
"voucher_platform_amount",
"discount_seller_amount",
"discount_platform_amount",
"wh_exported_at",
"wh_imported_at",
"sme_warehouse_id",
"is_gift",
"sme_product_name",
"sme_product_sku",
"sme_variant_name",
"sme_variant_sku",
"sme_variant_full_name"
],
"transform": {
"mapping": {
"weight": {
"type": "float"
}
}
},
"relationship": {
"variant": "object",
"type": "one_to_many",
"foreign_key": {
"parent": ["id" ],
"child": ["package_id"]
}
},
**"children": [
{
"table": "sc_order_item_combo",
"primary_key": ["id"],
"columns": [
"id",
"order_item_id"
],
"relationship": {
"variant": "object",
"type": "one_to_many",
"foreign_key": {
"parent": ["sc_order_item.id" ],
"child": ["order_item_id"]
}
}**
}
]
}
]
}
}
]
SQL generated:SELECT anon_1."JSON_BUILD_ARRAY_1", anon_1.anon_2, anon_1.id, anon_1.sme_id
FROM (SELECT JSON_BUILD_ARRAY(anon_3._keys, anon_4._keys, anon_5._keys) AS "JSON_BUILD_ARRAY_1", CAST(JSON_BUILD_OBJECT('id', sc_logistics_packages_1.id, 'connector_channel_code', sc_logistics_packages_1.connector_channel_code, 'store_id', sc_logistics_packages_1.store_id, 'sme_id', sc_logistics_packages_1.sme_id, 'is_gsgn', sc_logistics_packages_1.is_gsgn, 'is_pre_order', sc_logistics_packages_1.is_pre_order, 'package_number', sc_logistics_packages_1.package_number, 'logistics_status', sc_logistics_packages_1.logistics_status, 'order_id', sc_logistics_packages_1.order_id, 'sc_warehouse_id', sc_logistics_packages_1.sc_warehouse_id, 'sme_warehouse_id', sc_logistics_packages_1.sme_warehouse_id, 'shipping_carrier', sc_logistics_packages_1.shipping_carrier, 'tracking_number', sc_logistics_packages_1.tracking_number, 'created_at', sc_logistics_packages_1.created_at, 'updated_at', sc_logistics_packages_1.updated_at, 'print_status', sc_logistics_packages_1.print_status, 'pack_status', sc_logistics_packages_1.pack_status, 'pack_abnormal', sc_logistics_packages_1.pack_abnormal, 'create_doc_status', sc_logistics_packages_1.create_doc_status, 'warehouse_error_code', sc_logistics_packages_1.warehouse_error_code, 'warehouse_error_message', sc_logistics_packages_1.warehouse_error_message, 'connector_channel_error', sc_logistics_packages_1.connector_channel_error, 'order_at', sc_logistics_packages_1.order_at, 's3_document', sc_logistics_packages_1.s3_document, 'ref_warehouse_id', sc_logistics_packages_1.ref_warehouse_id, 'wh_export_code', sc_logistics_packages_1.wh_export_code, 'wh_import_code', sc_logistics_packages_1.wh_import_code, 'warehouse_code', sc_logistics_packages_1.warehouse_code, 'package_weight', sc_logistics_packages_1.package_weight, 'package_length', sc_logistics_packages_1.package_length, 'package_width', sc_logistics_packages_1.package_width, 'package_height', sc_logistics_packages_1.package_height, 'logistics_system', sc_logistics_packages_1.logistics_system, 'provider_or_id', sc_logistics_packages_1.provider_or_id, 'provider_or_status', sc_logistics_packages_1.provider_or_status, 'provider_or_error', sc_logistics_packages_1.provider_or_error, 'provider_last_synced_at', sc_logistics_packages_1.provider_last_synced_at, 'fulfillment_provider_wh_code', sc_logistics_packages_1.fulfillment_provider_wh_code, 'ref_order_id', sc_logistics_packages_1.ref_order_id, 'shipment_label_status', sc_logistics_packages_1.shipment_label_status, 'provider_payload', sc_logistics_packages_1.provider_payload, 'provider_shipment_status', sc_logistics_packages_1.provider_shipment_status, 'system_package_number', sc_logistics_packages_1.system_package_number, 'logistic_rts_status', sc_logistics_packages_1.logistic_rts_status, 'is_partial_cancelled', sc_logistics_packages_1.is_partial_cancelled, 'is_partial_cancelled_processed', sc_logistics_packages_1.is_partial_cancelled_processed, 'pack_no', sc_logistics_packages_1.pack_no, 'sf_session_pick_id', sc_logistics_packages_1.sf_session_pick_id, 'logistic_provider_connected_id', sc_logistics_packages_1.logistic_provider_connected_id, 'logistic_provider_status', sc_logistics_packages_1.logistic_provider_status) AS JSONB) || CAST(JSON_BUILD_OBJECT('logistic_provider_error', sc_logistics_packages_1.logistic_provider_error, 'shipping_service', sc_logistics_packages_1.shipping_service, 'shipping_type', sc_logistics_packages_1.shipping_type, 'shipping_rule_check', sc_logistics_packages_1.shipping_rule_check, 'warehouse_step', sc_logistics_packages_1.warehouse_step, 'warehouse_last_action', sc_logistics_packages_1.warehouse_last_action, 'warehouse_bill_code', sc_logistics_packages_1.warehouse_bill_code, 'import_bill_id', sc_logistics_packages_1.import_bill_id, 'warehouse_bill_id', sc_logistics_packages_1.warehouse_bill_id, 'sf_handover_id', sc_logistics_packages_1.sf_handover_id, 'provider_error_code', sc_logistics_packages_1.provider_error_code, 'wms_hold', sc_logistics_packages_1.wms_hold, 'sf_received_id', sc_logistics_packages_1.sf_received_id, 'ref_gsgn', sc_logistics_packages_1.ref_gsgn, 'logistic_synced_seller', sc_logistics_packages_1.logistic_synced_seller, 'pick_shift_id', sc_logistics_packages_1.pick_shift_id, 'date_pick_shift', sc_logistics_packages_1.date_pick_shift, 'pick_work_shift', sc_logistics_packages_1.pick_work_shift, 'date_delivery_shift', sc_logistics_packages_1.date_delivery_shift, 'delivery_work_shift', sc_logistics_packages_1.delivery_work_shift, 'date_delivery_id', sc_logistics_packages_1.date_delivery_id, 'station_id', sc_logistics_packages_1.station_id, 'count_sme_variant', sc_logistics_packages_1.count_sme_variant, 'source', sc_logistics_packages_1.source, 'prepared_at', sc_logistics_packages_1.prepared_at, 'reauto_wh_no', sc_logistics_packages_1.reauto_wh_no, 'last_set_sme_wh', sc_logistics_packages_1.last_set_sme_wh, 'old_logistics', sc_logistics_packages_1.old_logistics, 'logistic_provider_shop_id', sc_logistics_packages_1.logistic_provider_shop_id, 'fulfillment_provider_type', sc_logistics_packages_1.fulfillment_provider_type, 'fulfillment_provider_connected_id', sc_logistics_packages_1.fulfillment_provider_connected_id, 'list_sme_wh_selected', sc_logistics_packages_1.list_sme_wh_selected, 'warning_wh_step', sc_logistics_packages_1.warning_wh_step, 'cod', sc_logistics_packages_1.cod, 'order', anon_3."order", 'orderItems', anon_4."orderItems", 'sc_package_tags', anon_5.sc_package_tags) AS JSONB) AS anon_2, sc_logistics_packages_1.id AS id, sc_logistics_packages_1.sme_id AS sme_id
FROM public.sc_logistics_packages AS sc_logistics_packages_1 LEFT OUTER JOIN LATERAL (SELECT CAST(JSON_BUILD_OBJECT('sc_order', CAST(JSON_BUILD_OBJECT('id', JSON_BUILD_ARRAY(sc_order_1.id), 'sme_id', JSON_BUILD_ARRAY(sc_order_1.sme_id)) AS JSONB)) AS JSONB) AS _keys, (CAST(JSON_BUILD_OBJECT('id', sc_order_1.id, 'store_id', sc_order_1.store_id, 'sme_id', sc_order_1.sme_id, 'shipped_at', sc_order_1.shipped_at, 'is_gsgn', sc_order_1.is_gsgn, 'connector_channel_code', sc_order_1.connector_channel_code, 'ref_id', sc_order_1.ref_id, 'ref_store_id', sc_order_1.ref_store_id, 'ref_number', sc_order_1.ref_number, 'currency_code', sc_order_1.currency_code, 'is_paid', sc_order_1.is_paid, 'verified_flags', sc_order_1.verified_flags, 'paid_price', sc_order_1.paid_price, 'original_price', sc_order_1.original_price, 'platform_service_fee', sc_order_1.platform_service_fee, 'seller_transaction_fee', sc_order_1.seller_transaction_fee, 'promotion_seller_amount', sc_order_1.promotion_seller_amount, 'promotion_platform_amount', sc_order_1.promotion_platform_amount, 'shipping_original_fee', sc_order_1.shipping_original_fee, 'paid_shipping_fee', sc_order_1.paid_shipping_fee, 'shipping_discount_seller_fee', sc_order_1.shipping_discount_seller_fee, 'shipping_discount_platform_fee', sc_order_1.shipping_discount_platform_fee, 'buyer_transaction_fee', sc_order_1.buyer_transaction_fee, 'seller_discount_amount', sc_order_1.seller_discount_amount, 'platform_discount', sc_order_1.platform_discount, 'shipping_fee_discount_from_3pl', sc_order_1.shipping_fee_discount_from_3pl, 'platform_commission_fee', sc_order_1.platform_commission_fee, 'seller_coin_cash_back', sc_order_1.seller_coin_cash_back, 'ship_expired_at', sc_order_1.ship_expired_at, 'other_fee', sc_order_1.other_fee, 'cancel_by', sc_order_1.cancel_by, 'cancel_reason', sc_order_1.cancel_reason, 'buyer_cancel_reason', sc_order_1.buyer_cancel_reason, 'paid_at', sc_order_1.paid_at, 'order_at', sc_order_1.order_at, 'update_time', sc_order_1.update_time, 'payment_method', sc_order_1.payment_method, 'message_to_seller', sc_order_1.message_to_seller, 'note', sc_order_1.note, 'customer_id', sc_order_1.customer_id, 'status', sc_order_1.status, 'platform_status', sc_order_1.platform_status, 'recipient_address_id', sc_order_1.recipient_address_id, 'created_at', sc_order_1.created_at, 'updated_at', sc_order_1.updated_at, 'total_discount', sc_order_1.total_discount, 'return_at', sc_order_1.return_at, 'cancel_at', sc_order_1.cancel_at, 'load_status', sc_order_1.load_status, 'sync_error', sc_order_1.sync_error) AS JSONB) || CAST(JSON_BUILD_OBJECT('tts_expired', sc_order_1.tts_expired, 'p_delivery_method', sc_order_1.p_delivery_method, 'p_time_id', sc_order_1.p_time_id, 'p_address_id', sc_order_1.p_address_id, 'platform_status_text', sc_order_1.platform_status_text, 'logistic_fail', sc_order_1.logistic_fail, 'return_process_status', sc_order_1.return_process_status, 'returned_time', sc_order_1.returned_time, 'customer_username', sc_order_1.customer_username, 'seller_revenue', sc_order_1.seller_revenue, 'source', sc_order_1.source, 'is_connected', sc_order_1.is_connected, 'received_at', sc_order_1.received_at, 'completed_at', sc_order_1.completed_at, 'sme_note', sc_order_1.sme_note, 'payment_transaction_code', sc_order_1.payment_transaction_code, 'person_in_charge', sc_order_1.person_in_charge, 'last_wh_exported_at', sc_order_1.last_wh_exported_at, 'last_wh_imported_at', sc_order_1.last_wh_imported_at, 'shipping_fee_by', sc_order_1.shipping_fee_by, 'delivery_fail_at', sc_order_1.delivery_fail_at, 'fulfillment_provider_wh_code', sc_order_1.fulfillment_provider_wh_code, 'fulfillment_provider_type', sc_order_1.fulfillment_provider_type, 'fulfillment_provider_connected_id', sc_order_1.fulfillment_provider_connected_id, 'related_order_id', sc_order_1.related_order_id, 'related_order', sc_order_1.related_order, 'after_sale_type', sc_order_1.after_sale_type, 'abnormal', sc_order_1.abnormal, 'change_detail', sc_order_1.change_detail, 'is_auto_routing', sc_order_1.is_auto_routing, 'is_pre_order', sc_order_1.is_pre_order, 'affiliate_id', sc_order_1.affiliate_id, 'has_invoice', sc_order_1.has_invoice, 'marketing_staff', sc_order_1.marketing_staff, 'sales_person', sc_order_1.sales_person, 'utm', sc_order_1.utm, 'is_cod', sc_order_1.is_cod, 'is_auto_gen_ref_id', sc_order_1.is_auto_gen_ref_id, 'loyalty_point', sc_order_1.loyalty_point, 'loyalty_point_earn', sc_order_1.loyalty_point_earn, 'loyalty_price', sc_order_1.loyalty_price, 'loyalty_id', sc_order_1.loyalty_id, 'rts_sla_time', sc_order_1.rts_sla_time, 'has_shipping_config_fee', sc_order_1.has_shipping_config_fee, 'shipping_fee_gap', sc_order_1.shipping_fee_gap, 'shipping_config_fee', sc_order_1.shipping_config_fee, 'ref_gsgn', sc_order_1.ref_gsgn, 'customer_new', sc_order_1.customer_new, 'shipment_param_payload', sc_order_1.shipment_param_payload, 'shipment_param_need_load', sc_order_1.shipment_param_need_load) AS JSONB)) || CAST(JSON_BUILD_OBJECT('is_kol', sc_order_1.is_kol, 'reverse_request_time', sc_order_1.reverse_request_time, 'dispute_status', sc_order_1.dispute_status, 'dispute_deadline', sc_order_1.dispute_deadline, 'is_damaged', sc_order_1.is_damaged, 'dispute_fail_reason', sc_order_1.dispute_fail_reason, 'sme_cancel_reason_note', sc_order_1.sme_cancel_reason_note, 'sme_cancel_reason_type', sc_order_1.sme_cancel_reason_type, 'sme_cancel_order_video_url', sc_order_1.sme_cancel_order_video_url, 'sme_cancel_order_additional_videos', sc_order_1.sme_cancel_order_additional_videos) AS JSONB) AS "order", sc_order_1.id AS id
FROM public.sc_order AS sc_order_1
WHERE sc_order_1.id = sc_logistics_packages_1.order_id) AS anon_3 ON anon_3.id = sc_logistics_packages_1.order_id LEFT OUTER JOIN LATERAL (SELECT CAST(JSON_BUILD_OBJECT('sc_order_item', JSON_AGG(CAST(JSON_BUILD_OBJECT('id', JSON_BUILD_ARRAY(sc_order_item_1.id), 'sme_id', JSON_BUILD_ARRAY(sc_order_item_1.sme_id)) AS JSONB))) AS JSONB) || CAST(JSON_AGG(anon_6._keys) AS JSONB) AS _keys, JSON_AGG(CAST(JSON_BUILD_OBJECT('id', sc_order_item_1.id, 'store_id', sc_order_item_1.store_id, 'sme_id', sc_order_item_1.sme_id, 'ref_order_id', sc_order_item_1.ref_order_id, 'ref_id', sc_order_item_1.ref_id, 'list_ref_id', sc_order_item_1.list_ref_id, 'order_id', sc_order_item_1.order_id, 'connector_channel_code', sc_order_item_1.connector_channel_code, 'ref_product_id', sc_order_item_1.ref_product_id, 'sc_product_id', sc_order_item_1.sc_product_id, 'ref_variant_id', sc_order_item_1.ref_variant_id, 'sc_variant_id', sc_order_item_1.sc_variant_id, 'product_name', sc_order_item_1.product_name, 'variant_name', sc_order_item_1.variant_name, 'variant_image', sc_order_item_1.variant_image, 'variant_sku', sc_order_item_1.variant_sku, 'quantity_purchased', sc_order_item_1.quantity_purchased, 'order_at', sc_order_item_1.order_at, 'original_price', sc_order_item_1.original_price, 'paid_price', sc_order_item_1.paid_price, 'discounted_price', sc_order_item_1.discounted_price, 'reason', sc_order_item_1.reason, 'reason_detail', sc_order_item_1.reason_detail, 'created_at', sc_order_item_1.created_at, 'updated_at', sc_order_item_1.updated_at, 'sme_product_id', sc_order_item_1.sme_product_id, 'sme_variant_id', sc_order_item_1.sme_variant_id, 'warehouse_error_code', sc_order_item_1.warehouse_error_code, 'warehouse_error_message', sc_order_item_1.warehouse_error_message, 'warehouse_step', sc_order_item_1.warehouse_step, 'warehouse_last_action', sc_order_item_1.warehouse_last_action, 'package_id', sc_order_item_1.package_id, 'is_combo', sc_order_item_1.is_combo, 'voucher_seller_amount', sc_order_item_1.voucher_seller_amount, 'voucher_platform_amount', sc_order_item_1.voucher_platform_amount, 'discount_seller_amount', sc_order_item_1.discount_seller_amount, 'discount_platform_amount', sc_order_item_1.discount_platform_amount, 'wh_exported_at', sc_order_item_1.wh_exported_at, 'wh_imported_at', sc_order_item_1.wh_imported_at, 'sme_warehouse_id', sc_order_item_1.sme_warehouse_id, 'is_gift', sc_order_item_1.is_gift, 'sme_product_name', sc_order_item_1.sme_product_name, 'sme_product_sku', sc_order_item_1.sme_product_sku, 'sme_variant_name', sc_order_item_1.sme_variant_name, 'sme_variant_sku', sc_order_item_1.sme_variant_sku, 'sme_variant_full_name', sc_order_item_1.sme_variant_full_name, 'sme_variant_id_first', sc_order_item_1.sme_variant_id_first, 'weight', sc_order_item_1.weight, 'length', sc_order_item_1.length, 'width', sc_order_item_1.width) AS JSONB) || CAST(JSON_BUILD_OBJECT('height', sc_order_item_1.height, 'list_expiry', sc_order_item_1.list_expiry, 'gift_campaign_id', sc_order_item_1.gift_campaign_id, 'loyalty_price', sc_order_item_1.loyalty_price, 'sc_order_item_combo', anon_6.sc_order_item_combo) AS JSONB)) AS "orderItems", sc_order_item_1.package_id AS package_id
FROM public.sc_order_item AS sc_order_item_1 LEFT OUTER JOIN LATERAL (SELECT CAST(JSON_BUILD_OBJECT('sc_order_item_combo', JSON_AGG(CAST(JSON_BUILD_OBJECT('id', sc_order_item_combo_1.id) AS JSONB))) AS JSONB) AS _keys, JSON_AGG(CAST(JSON_BUILD_OBJECT('id', sc_order_item_combo_1.id, 'order_item_id', sc_order_item_combo_1.order_item_id, 'store_id', sc_order_item_combo_1.store_id, 'sme_id', sc_order_item_combo_1.sme_id, 'connector_channel_code', sc_order_item_combo_1.connector_channel_code, 'sme_variant_id', sc_order_item_combo_1.sme_variant_id, 'purchased_quantity', sc_order_item_combo_1.purchased_quantity, 'warehouse_error_code', sc_order_item_combo_1.warehouse_error_code, 'warehouse_error_message', sc_order_item_combo_1.warehouse_error_message, 'warehouse_step', sc_order_item_combo_1.warehouse_step, 'warehouse_last_action', sc_order_item_combo_1.warehouse_last_action, 'order_id', sc_order_item_combo_1.order_id, 'wh_exported_at', sc_order_item_combo_1.wh_exported_at, 'wh_imported_at', sc_order_item_combo_1.wh_imported_at, 'order_at', sc_order_item_combo_1.order_at, 'sme_variant_sku', sc_order_item_combo_1.sme_variant_sku, 'sme_variant_id_first', sc_order_item_combo_1.sme_variant_id_first, 'list_expiry', sc_order_item_combo_1.list_expiry, 'package_id', sc_order_item_combo_1.package_id) AS JSONB)) AS sc_order_item_combo, sc_order_item_combo_1.order_item_id AS order_item_id
FROM public.sc_order_item_combo AS sc_order_item_combo_1
WHERE sc_order_item_combo_1.order_item_id = sc_order_item_1.package_id GROUP BY sc_order_item_combo_1.order_item_id) AS anon_6 ON anon_6.order_item_id = sc_order_item_1.package_id
WHERE sc_order_item_1.package_id = sc_logistics_packages_1.id GROUP BY sc_order_item_1.package_id) AS anon_4 ON anon_4.package_id = sc_logistics_packages_1.id LEFT OUTER JOIN LATERAL (SELECT CAST(JSON_BUILD_OBJECT('sc_package_tags', JSON_AGG(CAST(JSON_BUILD_OBJECT('id', JSON_BUILD_ARRAY(sc_package_tags_1.id)) AS JSONB))) AS JSONB) AS _keys, JSON_AGG(CAST(JSON_BUILD_OBJECT('id', sc_package_tags_1.id, 'sme_id', sc_package_tags_1.sme_id, 'package_id', sc_package_tags_1.package_id, 'tag_id', sc_package_tags_1.tag_id) AS JSONB)) AS sc_package_tags, sc_package_tags_1.package_id AS package_id
FROM public.sc_package_tags AS sc_package_tags_1
WHERE sc_package_tags_1.package_id = sc_logistics_packages_1.id GROUP BY sc_package_tags_1.package_id) AS anon_5 ON anon_5.package_id = sc_logistics_packages_1.id
WHERE (sc_logistics_packages_1.ctid = ANY (ARRAY((SELECT CAST('(20386,' || s || ')' AS TID) AS anon_7
FROM (VALUES (8)) AS s (s)))) OR sc_logistics_packages_1.ctid = ANY `
PGSync version: latest
Postgres version: 14
Elasticsearch/OpenSearch version: 2.17.1
Redis version: 7.4.0
Python version: 3.10.12
why sql generate condition WHERE sc_order_item_combo_1.order_item_id = sc_order_item_1.package_id . expected: WHERE sc_order_item_combo_1.order_item_id = sc_order_item_1.id
Thanks
Schema
`[
{
"database": "order_connector",
"index": "oms_packages",
"nodes": {
"table": "sc_logistics_packages",
"primary_key": ["id"],
"schema": "public",
"columns": [
"id",
"connector_channel_code",
"store_id",
"sme_id",
"package_number",
"logistics_status",
"order_id",
"sc_warehouse_id",
"sme_warehouse_id",
"shipping_carrier",
"tracking_number",
"created_at",
"updated_at",
"print_status",
"pack_status",
"pack_abnormal",
"create_doc_status",
"warehouse_error_code",
"warehouse_error_message",
"connector_channel_error",
"order_at",
"s3_document",
"ref_warehouse_id",
"wh_export_code",
"wh_import_code",
"warehouse_code",
"package_weight",
"package_length",
"package_width",
"package_height",
"logistics_system",
"provider_or_id",
"provider_or_status",
"provider_or_error",
"provider_last_synced_at",
"fulfillment_provider_wh_code",
"ref_order_id",
"shipment_label_status",
"provider_payload",
"provider_shipment_status",
"system_package_number",
"logistic_rts_status",
"is_partial_cancelled",
"is_partial_cancelled_processed",
"pack_no"
],
"children": [
{
"table": "sc_order",
"primary_key": ["id"],
}
]
SQL generated:SELECT anon_1."JSON_BUILD_ARRAY_1", anon_1.anon_2, anon_1.id, anon_1.sme_idFROM (SELECT JSON_BUILD_ARRAY(anon_3._keys, anon_4._keys, anon_5._keys) AS "JSON_BUILD_ARRAY_1", CAST(JSON_BUILD_OBJECT('id', sc_logistics_packages_1.id, 'connector_channel_code', sc_logistics_packages_1.connector_channel_code, 'store_id', sc_logistics_packages_1.store_id, 'sme_id', sc_logistics_packages_1.sme_id, 'is_gsgn', sc_logistics_packages_1.is_gsgn, 'is_pre_order', sc_logistics_packages_1.is_pre_order, 'package_number', sc_logistics_packages_1.package_number, 'logistics_status', sc_logistics_packages_1.logistics_status, 'order_id', sc_logistics_packages_1.order_id, 'sc_warehouse_id', sc_logistics_packages_1.sc_warehouse_id, 'sme_warehouse_id', sc_logistics_packages_1.sme_warehouse_id, 'shipping_carrier', sc_logistics_packages_1.shipping_carrier, 'tracking_number', sc_logistics_packages_1.tracking_number, 'created_at', sc_logistics_packages_1.created_at, 'updated_at', sc_logistics_packages_1.updated_at, 'print_status', sc_logistics_packages_1.print_status, 'pack_status', sc_logistics_packages_1.pack_status, 'pack_abnormal', sc_logistics_packages_1.pack_abnormal, 'create_doc_status', sc_logistics_packages_1.create_doc_status, 'warehouse_error_code', sc_logistics_packages_1.warehouse_error_code, 'warehouse_error_message', sc_logistics_packages_1.warehouse_error_message, 'connector_channel_error', sc_logistics_packages_1.connector_channel_error, 'order_at', sc_logistics_packages_1.order_at, 's3_document', sc_logistics_packages_1.s3_document, 'ref_warehouse_id', sc_logistics_packages_1.ref_warehouse_id, 'wh_export_code', sc_logistics_packages_1.wh_export_code, 'wh_import_code', sc_logistics_packages_1.wh_import_code, 'warehouse_code', sc_logistics_packages_1.warehouse_code, 'package_weight', sc_logistics_packages_1.package_weight, 'package_length', sc_logistics_packages_1.package_length, 'package_width', sc_logistics_packages_1.package_width, 'package_height', sc_logistics_packages_1.package_height, 'logistics_system', sc_logistics_packages_1.logistics_system, 'provider_or_id', sc_logistics_packages_1.provider_or_id, 'provider_or_status', sc_logistics_packages_1.provider_or_status, 'provider_or_error', sc_logistics_packages_1.provider_or_error, 'provider_last_synced_at', sc_logistics_packages_1.provider_last_synced_at, 'fulfillment_provider_wh_code', sc_logistics_packages_1.fulfillment_provider_wh_code, 'ref_order_id', sc_logistics_packages_1.ref_order_id, 'shipment_label_status', sc_logistics_packages_1.shipment_label_status, 'provider_payload', sc_logistics_packages_1.provider_payload, 'provider_shipment_status', sc_logistics_packages_1.provider_shipment_status, 'system_package_number', sc_logistics_packages_1.system_package_number, 'logistic_rts_status', sc_logistics_packages_1.logistic_rts_status, 'is_partial_cancelled', sc_logistics_packages_1.is_partial_cancelled, 'is_partial_cancelled_processed', sc_logistics_packages_1.is_partial_cancelled_processed, 'pack_no', sc_logistics_packages_1.pack_no, 'sf_session_pick_id', sc_logistics_packages_1.sf_session_pick_id, 'logistic_provider_connected_id', sc_logistics_packages_1.logistic_provider_connected_id, 'logistic_provider_status', sc_logistics_packages_1.logistic_provider_status) AS JSONB) || CAST(JSON_BUILD_OBJECT('logistic_provider_error', sc_logistics_packages_1.logistic_provider_error, 'shipping_service', sc_logistics_packages_1.shipping_service, 'shipping_type', sc_logistics_packages_1.shipping_type, 'shipping_rule_check', sc_logistics_packages_1.shipping_rule_check, 'warehouse_step', sc_logistics_packages_1.warehouse_step, 'warehouse_last_action', sc_logistics_packages_1.warehouse_last_action, 'warehouse_bill_code', sc_logistics_packages_1.warehouse_bill_code, 'import_bill_id', sc_logistics_packages_1.import_bill_id, 'warehouse_bill_id', sc_logistics_packages_1.warehouse_bill_id, 'sf_handover_id', sc_logistics_packages_1.sf_handover_id, 'provider_error_code', sc_logistics_packages_1.provider_error_code, 'wms_hold', sc_logistics_packages_1.wms_hold, 'sf_received_id', sc_logistics_packages_1.sf_received_id, 'ref_gsgn', sc_logistics_packages_1.ref_gsgn, 'logistic_synced_seller', sc_logistics_packages_1.logistic_synced_seller, 'pick_shift_id', sc_logistics_packages_1.pick_shift_id, 'date_pick_shift', sc_logistics_packages_1.date_pick_shift, 'pick_work_shift', sc_logistics_packages_1.pick_work_shift, 'date_delivery_shift', sc_logistics_packages_1.date_delivery_shift, 'delivery_work_shift', sc_logistics_packages_1.delivery_work_shift, 'date_delivery_id', sc_logistics_packages_1.date_delivery_id, 'station_id', sc_logistics_packages_1.station_id, 'count_sme_variant', sc_logistics_packages_1.count_sme_variant, 'source', sc_logistics_packages_1.source, 'prepared_at', sc_logistics_packages_1.prepared_at, 'reauto_wh_no', sc_logistics_packages_1.reauto_wh_no, 'last_set_sme_wh', sc_logistics_packages_1.last_set_sme_wh, 'old_logistics', sc_logistics_packages_1.old_logistics, 'logistic_provider_shop_id', sc_logistics_packages_1.logistic_provider_shop_id, 'fulfillment_provider_type', sc_logistics_packages_1.fulfillment_provider_type, 'fulfillment_provider_connected_id', sc_logistics_packages_1.fulfillment_provider_connected_id, 'list_sme_wh_selected', sc_logistics_packages_1.list_sme_wh_selected, 'warning_wh_step', sc_logistics_packages_1.warning_wh_step, 'cod', sc_logistics_packages_1.cod, 'order', anon_3."order", 'orderItems', anon_4."orderItems", 'sc_package_tags', anon_5.sc_package_tags) AS JSONB) AS anon_2, sc_logistics_packages_1.id AS id, sc_logistics_packages_1.sme_id AS sme_id
FROM public.sc_logistics_packages AS sc_logistics_packages_1 LEFT OUTER JOIN LATERAL (SELECT CAST(JSON_BUILD_OBJECT('sc_order', CAST(JSON_BUILD_OBJECT('id', JSON_BUILD_ARRAY(sc_order_1.id), 'sme_id', JSON_BUILD_ARRAY(sc_order_1.sme_id)) AS JSONB)) AS JSONB) AS _keys, (CAST(JSON_BUILD_OBJECT('id', sc_order_1.id, 'store_id', sc_order_1.store_id, 'sme_id', sc_order_1.sme_id, 'shipped_at', sc_order_1.shipped_at, 'is_gsgn', sc_order_1.is_gsgn, 'connector_channel_code', sc_order_1.connector_channel_code, 'ref_id', sc_order_1.ref_id, 'ref_store_id', sc_order_1.ref_store_id, 'ref_number', sc_order_1.ref_number, 'currency_code', sc_order_1.currency_code, 'is_paid', sc_order_1.is_paid, 'verified_flags', sc_order_1.verified_flags, 'paid_price', sc_order_1.paid_price, 'original_price', sc_order_1.original_price, 'platform_service_fee', sc_order_1.platform_service_fee, 'seller_transaction_fee', sc_order_1.seller_transaction_fee, 'promotion_seller_amount', sc_order_1.promotion_seller_amount, 'promotion_platform_amount', sc_order_1.promotion_platform_amount, 'shipping_original_fee', sc_order_1.shipping_original_fee, 'paid_shipping_fee', sc_order_1.paid_shipping_fee, 'shipping_discount_seller_fee', sc_order_1.shipping_discount_seller_fee, 'shipping_discount_platform_fee', sc_order_1.shipping_discount_platform_fee, 'buyer_transaction_fee', sc_order_1.buyer_transaction_fee, 'seller_discount_amount', sc_order_1.seller_discount_amount, 'platform_discount', sc_order_1.platform_discount, 'shipping_fee_discount_from_3pl', sc_order_1.shipping_fee_discount_from_3pl, 'platform_commission_fee', sc_order_1.platform_commission_fee, 'seller_coin_cash_back', sc_order_1.seller_coin_cash_back, 'ship_expired_at', sc_order_1.ship_expired_at, 'other_fee', sc_order_1.other_fee, 'cancel_by', sc_order_1.cancel_by, 'cancel_reason', sc_order_1.cancel_reason, 'buyer_cancel_reason', sc_order_1.buyer_cancel_reason, 'paid_at', sc_order_1.paid_at, 'order_at', sc_order_1.order_at, 'update_time', sc_order_1.update_time, 'payment_method', sc_order_1.payment_method, 'message_to_seller', sc_order_1.message_to_seller, 'note', sc_order_1.note, 'customer_id', sc_order_1.customer_id, 'status', sc_order_1.status, 'platform_status', sc_order_1.platform_status, 'recipient_address_id', sc_order_1.recipient_address_id, 'created_at', sc_order_1.created_at, 'updated_at', sc_order_1.updated_at, 'total_discount', sc_order_1.total_discount, 'return_at', sc_order_1.return_at, 'cancel_at', sc_order_1.cancel_at, 'load_status', sc_order_1.load_status, 'sync_error', sc_order_1.sync_error) AS JSONB) || CAST(JSON_BUILD_OBJECT('tts_expired', sc_order_1.tts_expired, 'p_delivery_method', sc_order_1.p_delivery_method, 'p_time_id', sc_order_1.p_time_id, 'p_address_id', sc_order_1.p_address_id, 'platform_status_text', sc_order_1.platform_status_text, 'logistic_fail', sc_order_1.logistic_fail, 'return_process_status', sc_order_1.return_process_status, 'returned_time', sc_order_1.returned_time, 'customer_username', sc_order_1.customer_username, 'seller_revenue', sc_order_1.seller_revenue, 'source', sc_order_1.source, 'is_connected', sc_order_1.is_connected, 'received_at', sc_order_1.received_at, 'completed_at', sc_order_1.completed_at, 'sme_note', sc_order_1.sme_note, 'payment_transaction_code', sc_order_1.payment_transaction_code, 'person_in_charge', sc_order_1.person_in_charge, 'last_wh_exported_at', sc_order_1.last_wh_exported_at, 'last_wh_imported_at', sc_order_1.last_wh_imported_at, 'shipping_fee_by', sc_order_1.shipping_fee_by, 'delivery_fail_at', sc_order_1.delivery_fail_at, 'fulfillment_provider_wh_code', sc_order_1.fulfillment_provider_wh_code, 'fulfillment_provider_type', sc_order_1.fulfillment_provider_type, 'fulfillment_provider_connected_id', sc_order_1.fulfillment_provider_connected_id, 'related_order_id', sc_order_1.related_order_id, 'related_order', sc_order_1.related_order, 'after_sale_type', sc_order_1.after_sale_type, 'abnormal', sc_order_1.abnormal, 'change_detail', sc_order_1.change_detail, 'is_auto_routing', sc_order_1.is_auto_routing, 'is_pre_order', sc_order_1.is_pre_order, 'affiliate_id', sc_order_1.affiliate_id, 'has_invoice', sc_order_1.has_invoice, 'marketing_staff', sc_order_1.marketing_staff, 'sales_person', sc_order_1.sales_person, 'utm', sc_order_1.utm, 'is_cod', sc_order_1.is_cod, 'is_auto_gen_ref_id', sc_order_1.is_auto_gen_ref_id, 'loyalty_point', sc_order_1.loyalty_point, 'loyalty_point_earn', sc_order_1.loyalty_point_earn, 'loyalty_price', sc_order_1.loyalty_price, 'loyalty_id', sc_order_1.loyalty_id, 'rts_sla_time', sc_order_1.rts_sla_time, 'has_shipping_config_fee', sc_order_1.has_shipping_config_fee, 'shipping_fee_gap', sc_order_1.shipping_fee_gap, 'shipping_config_fee', sc_order_1.shipping_config_fee, 'ref_gsgn', sc_order_1.ref_gsgn, 'customer_new', sc_order_1.customer_new, 'shipment_param_payload', sc_order_1.shipment_param_payload, 'shipment_param_need_load', sc_order_1.shipment_param_need_load) AS JSONB)) || CAST(JSON_BUILD_OBJECT('is_kol', sc_order_1.is_kol, 'reverse_request_time', sc_order_1.reverse_request_time, 'dispute_status', sc_order_1.dispute_status, 'dispute_deadline', sc_order_1.dispute_deadline, 'is_damaged', sc_order_1.is_damaged, 'dispute_fail_reason', sc_order_1.dispute_fail_reason, 'sme_cancel_reason_note', sc_order_1.sme_cancel_reason_note, 'sme_cancel_reason_type', sc_order_1.sme_cancel_reason_type, 'sme_cancel_order_video_url', sc_order_1.sme_cancel_order_video_url, 'sme_cancel_order_additional_videos', sc_order_1.sme_cancel_order_additional_videos) AS JSONB) AS "order", sc_order_1.id AS id
FROM public.sc_order AS sc_order_1
WHERE sc_order_1.id = sc_logistics_packages_1.order_id) AS anon_3 ON anon_3.id = sc_logistics_packages_1.order_id LEFT OUTER JOIN LATERAL (SELECT CAST(JSON_BUILD_OBJECT('sc_order_item', JSON_AGG(CAST(JSON_BUILD_OBJECT('id', JSON_BUILD_ARRAY(sc_order_item_1.id), 'sme_id', JSON_BUILD_ARRAY(sc_order_item_1.sme_id)) AS JSONB))) AS JSONB) || CAST(JSON_AGG(anon_6._keys) AS JSONB) AS _keys, JSON_AGG(CAST(JSON_BUILD_OBJECT('id', sc_order_item_1.id, 'store_id', sc_order_item_1.store_id, 'sme_id', sc_order_item_1.sme_id, 'ref_order_id', sc_order_item_1.ref_order_id, 'ref_id', sc_order_item_1.ref_id, 'list_ref_id', sc_order_item_1.list_ref_id, 'order_id', sc_order_item_1.order_id, 'connector_channel_code', sc_order_item_1.connector_channel_code, 'ref_product_id', sc_order_item_1.ref_product_id, 'sc_product_id', sc_order_item_1.sc_product_id, 'ref_variant_id', sc_order_item_1.ref_variant_id, 'sc_variant_id', sc_order_item_1.sc_variant_id, 'product_name', sc_order_item_1.product_name, 'variant_name', sc_order_item_1.variant_name, 'variant_image', sc_order_item_1.variant_image, 'variant_sku', sc_order_item_1.variant_sku, 'quantity_purchased', sc_order_item_1.quantity_purchased, 'order_at', sc_order_item_1.order_at, 'original_price', sc_order_item_1.original_price, 'paid_price', sc_order_item_1.paid_price, 'discounted_price', sc_order_item_1.discounted_price, 'reason', sc_order_item_1.reason, 'reason_detail', sc_order_item_1.reason_detail, 'created_at', sc_order_item_1.created_at, 'updated_at', sc_order_item_1.updated_at, 'sme_product_id', sc_order_item_1.sme_product_id, 'sme_variant_id', sc_order_item_1.sme_variant_id, 'warehouse_error_code', sc_order_item_1.warehouse_error_code, 'warehouse_error_message', sc_order_item_1.warehouse_error_message, 'warehouse_step', sc_order_item_1.warehouse_step, 'warehouse_last_action', sc_order_item_1.warehouse_last_action, 'package_id', sc_order_item_1.package_id, 'is_combo', sc_order_item_1.is_combo, 'voucher_seller_amount', sc_order_item_1.voucher_seller_amount, 'voucher_platform_amount', sc_order_item_1.voucher_platform_amount, 'discount_seller_amount', sc_order_item_1.discount_seller_amount, 'discount_platform_amount', sc_order_item_1.discount_platform_amount, 'wh_exported_at', sc_order_item_1.wh_exported_at, 'wh_imported_at', sc_order_item_1.wh_imported_at, 'sme_warehouse_id', sc_order_item_1.sme_warehouse_id, 'is_gift', sc_order_item_1.is_gift, 'sme_product_name', sc_order_item_1.sme_product_name, 'sme_product_sku', sc_order_item_1.sme_product_sku, 'sme_variant_name', sc_order_item_1.sme_variant_name, 'sme_variant_sku', sc_order_item_1.sme_variant_sku, 'sme_variant_full_name', sc_order_item_1.sme_variant_full_name, 'sme_variant_id_first', sc_order_item_1.sme_variant_id_first, 'weight', sc_order_item_1.weight, 'length', sc_order_item_1.length, 'width', sc_order_item_1.width) AS JSONB) || CAST(JSON_BUILD_OBJECT('height', sc_order_item_1.height, 'list_expiry', sc_order_item_1.list_expiry, 'gift_campaign_id', sc_order_item_1.gift_campaign_id, 'loyalty_price', sc_order_item_1.loyalty_price, 'sc_order_item_combo', anon_6.sc_order_item_combo) AS JSONB)) AS "orderItems", sc_order_item_1.package_id AS package_id
FROM public.sc_order_item AS sc_order_item_1 LEFT OUTER JOIN LATERAL (SELECT CAST(JSON_BUILD_OBJECT('sc_order_item_combo', JSON_AGG(CAST(JSON_BUILD_OBJECT('id', sc_order_item_combo_1.id) AS JSONB))) AS JSONB) AS _keys, JSON_AGG(CAST(JSON_BUILD_OBJECT('id', sc_order_item_combo_1.id, 'order_item_id', sc_order_item_combo_1.order_item_id, 'store_id', sc_order_item_combo_1.store_id, 'sme_id', sc_order_item_combo_1.sme_id, 'connector_channel_code', sc_order_item_combo_1.connector_channel_code, 'sme_variant_id', sc_order_item_combo_1.sme_variant_id, 'purchased_quantity', sc_order_item_combo_1.purchased_quantity, 'warehouse_error_code', sc_order_item_combo_1.warehouse_error_code, 'warehouse_error_message', sc_order_item_combo_1.warehouse_error_message, 'warehouse_step', sc_order_item_combo_1.warehouse_step, 'warehouse_last_action', sc_order_item_combo_1.warehouse_last_action, 'order_id', sc_order_item_combo_1.order_id, 'wh_exported_at', sc_order_item_combo_1.wh_exported_at, 'wh_imported_at', sc_order_item_combo_1.wh_imported_at, 'order_at', sc_order_item_combo_1.order_at, 'sme_variant_sku', sc_order_item_combo_1.sme_variant_sku, 'sme_variant_id_first', sc_order_item_combo_1.sme_variant_id_first, 'list_expiry', sc_order_item_combo_1.list_expiry, 'package_id', sc_order_item_combo_1.package_id) AS JSONB)) AS sc_order_item_combo, sc_order_item_combo_1.order_item_id AS order_item_id
FROM public.sc_order_item_combo AS sc_order_item_combo_1
WHERE sc_order_item_combo_1.order_item_id = sc_order_item_1.package_id GROUP BY sc_order_item_combo_1.order_item_id) AS anon_6 ON anon_6.order_item_id = sc_order_item_1.package_id
WHERE sc_order_item_1.package_id = sc_logistics_packages_1.id GROUP BY sc_order_item_1.package_id) AS anon_4 ON anon_4.package_id = sc_logistics_packages_1.id LEFT OUTER JOIN LATERAL (SELECT CAST(JSON_BUILD_OBJECT('sc_package_tags', JSON_AGG(CAST(JSON_BUILD_OBJECT('id', JSON_BUILD_ARRAY(sc_package_tags_1.id)) AS JSONB))) AS JSONB) AS _keys, JSON_AGG(CAST(JSON_BUILD_OBJECT('id', sc_package_tags_1.id, 'sme_id', sc_package_tags_1.sme_id, 'package_id', sc_package_tags_1.package_id, 'tag_id', sc_package_tags_1.tag_id) AS JSONB)) AS sc_package_tags, sc_package_tags_1.package_id AS package_id
FROM public.sc_package_tags AS sc_package_tags_1
WHERE sc_package_tags_1.package_id = sc_logistics_packages_1.id GROUP BY sc_package_tags_1.package_id) AS anon_5 ON anon_5.package_id = sc_logistics_packages_1.id
WHERE (sc_logistics_packages_1.ctid = ANY (ARRAY((SELECT CAST('(20386,' || s || ')' AS TID) AS anon_7
FROM (VALUES (8)) AS s (s)))) OR sc_logistics_packages_1.ctid = ANY `