كيفية جلب المواضيع مرتبة حسب قيمة الحقل المخصص iso8601؟

مطور
1

مرحبًا،

لقد صادفت استعلامًا غريبًا لا أعرف حقًا كيف أعالجه باستخدام استعلام SQL أو Active Record فقط.

لدي مواضيع مع حقول مخصصة، وأحتاج إلى فرز البيانات باستخدام قيمة الحقل المخصص ‘importedSortDate’.

الكود الأولي:
Topic.where(‘category_id in (?)’, [cat_id])
.where(‘closed = ?’, false)
.order(‘created_at desc’)
.limit(10)

كيف يمكنني تعديل هذا الاستعلام أو إضافة شيء إليه لتحميل هذه الـ 10 مواضيع فقط في الذاكرة، وليس أكثر؟

بما أن قالب جدول الحقول المخصصة غريب بعض الشيء، بمعنى أنه يحتوي على عمود للاسم وعمود للقيمة، فأنا عالق قليلًا بشأن كيفية إدارة ذلك كله باستخدام استعلام قاعدة البيانات.

مشكلتي مع تحميل المزيد في الذاكرة هي أنه لكل موضوع أحمله في الذاكرة، يتم أيضًا تنفيذ جلب لفئة (category) ومستخدم (user) ومنشور (post).

أعمل مع 1000-2000 موضوع لكل فئة، ولدي 4 فئات أحتاج لجلب البيانات منها.
يتم تنفيذ الجلب بواسطة جميع المستخدمين، وليس مرة واحدة فقط.

كما أرى، لدي طريقتان للقيام بذلك حتى الآن (الطريقة الأولى لا تبدو سريعة بما يكفي بالنسبة لي):

  1. الحصول على معرفات (IDs) فقط لجميع المواضيع التي أهتم بها في طلب واحد، ثم استخدام هذه المعلومات بطريقة ما للربط مع قيمة ‘importedSortDate’، ثم إجراء الفرز في الذاكرة، ثم إجراء جلب آخر للمعرفات بنفس الترتيب الذي لدي فيه (مرة أخرى، لا أعرف حقًا كيف ستعمل هذه العملية بالضبط).
  2. إنشاء استعلام يربط بجدول الحقول المخصصة (لقد رأيت بعض الأمثلة لكنها كانت لقيم ثابتة في أعمدة الاسم والقيمة) وفرز كل هذا بناءً على الأعمدة حيث ‘name’ === ‘importedSortDate’ وعمود القيمة (سلسلة ISO8601).

كيف يمكنني تحقيق ذلك؟

ملاحظة: بعض المواضيع قد لا تحتوي على قيمة في الحقل المخصص ‘importedSortDate’، لذا يمكنني مجرد جلبها ووضعها حيثما أريد، لكنها حالة هامشية يمكنني التعامل معها. إذا لم تكن تحتوي على قيمة، فيجب أن تكون هي الإدخالات الأولى.

ملاحظة إضافية: أريد استخدام هذا الاستعلام لاحقًا كاستعلام للترقيم (pagination)، حيث أن المنطق مُنفذ بالفعل في هذا الجانب، أنا فقط بحاجة إلى تغيير الاستعلام الرئيسي لجلب المواضيع.

2

يجب أن يقوم limit(10) بذلك بالفعل :slight_smile:

هذا النوع من الحلول يجب أن ينجح: (لكنه لم يُختبر)

Topic.where("category_id in (?)", [cat_id])
         .where("closed = ?", false)
         .joins("LEFT JOIN topic_custom_fields import_tcf ON import_tcf.topic_id = topic.id AND topic_custom_fields.name = 'importedSortDate'")
         .order("TIMESTAMP import_tcf.value")
         .order("created_at desc")
         .limit(10)

المشكلة هي أن طلب PostgreSQL لتحويل سلسلة التاريخ إلى طابع زمني (timestamp) سيكون غير فعال للغاية. سيتعين عليه إجراء التحويل لكل عنوان على حدة أولاً، ثم اختيار أول 10 عناوين.

هناك عدة خيارات يمكنني التفكير فيها هنا - يمكنك تخزين التواريخ كـ “ثوانٍ منذ بداية العصر” بدلاً من تنسيق ISO8601. هذا سيجعل من السهل على PostgreSQL فرزها بشكل كبير. أو قد تتمكن من استخدام هجرة قاعدة بيانات لإضافة فهرس إلى جدول topic_custom_fields لحقل TIMESTAMP value.

4 إعجابات
3

أوه…

حسناً، هذا يعني ببساطة أنه بدلاً من استخدام iso8601، يجب عليّ حفظ عدد الثواني منذ بداية العصر (epoch) لضمان صحة مقارنة السلاسل النصية وترتيبها بشكل صحيح.

كنت منشغلاً جداً بالقلق من عدم قدرتي على الحصول على قيمة import_tcf، لدرجة أنني نسيت تماماً استخدام SQL الخام…

إعجاب واحد (1)
4

حسنًا … لقد وصلت إلى هنا:

 topics = Topic.where("topics.category_id in (?)", [7])
    .where("topics.closed = ?", false)
    .joins("LEFT JOIN topic_custom_fields custom_fields ON custom_fields.topic_id = topics.id AND custom_fields.name = '#{Constants::TOPIC_SORT_DATE}'")
    .order("coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc") # شكرًا @falco
  # .limit(10)

  array = topics.to_a.map do |t|
    next { id: t.id, createdAt: t.created_at, sortDate: t.custom_fields[Constants::TOPIC_SORT_DATE] }
  end
  puts array

مع المخرجات:

{:id=>25, :createdAt=>Thu, 14 May 2020 09:26:47 UTC +00:00, :sortDate=>nil}
{:id=>7017, :createdAt=>Tue, 06 Oct 2020 07:49:10 UTC +00:00, :sortDate=>"2011-01-02T22:00:00.000Z"}
{:id=>7016, :createdAt=>Tue, 06 Oct 2020 07:49:10 UTC +00:00, :sortDate=>"2011-01-02T22:00:00.000Z"}
{:id=>7058, :createdAt=>Tue, 06 Oct 2020 15:39:49 UTC +00:00, :sortDate=>"2010-02-02T00:00:00Z"}
{:id=>7008, :createdAt=>Tue, 06 Oct 2020 07:49:05 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7010, :createdAt=>Tue, 06 Oct 2020 07:49:06 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7011, :createdAt=>Tue, 06 Oct 2020 07:49:06 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7012, :createdAt=>Tue, 06 Oct 2020 07:49:07 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7013, :createdAt=>Tue, 06 Oct 2020 07:49:08 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7014, :createdAt=>Tue, 06 Oct 2020 07:49:08 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7015, :createdAt=>Tue, 06 Oct 2020 07:49:09 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7003, :createdAt=>Tue, 06 Oct 2020 07:49:01 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7004, :createdAt=>Tue, 06 Oct 2020 07:49:02 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7005, :createdAt=>Tue, 06 Oct 2020 07:49:03 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7006, :createdAt=>Tue, 06 Oct 2020 07:49:03 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7007, :createdAt=>Tue, 06 Oct 2020 07:49:04 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7009, :createdAt=>Tue, 06 Oct 2020 07:49:05 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7059, :createdAt=>Tue, 06 Oct 2020 15:49:16 UTC +00:00, :sortDate=>"2009-02-02T00:00:00Z"}
{:id=>7002, :createdAt=>Tue, 06 Oct 2020 07:49:01 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>7001, :createdAt=>Tue, 06 Oct 2020 07:49:00 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>6999, :createdAt=>Tue, 06 Oct 2020 07:48:59 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>6998, :createdAt=>Tue, 06 Oct 2020 07:48:58 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>7000, :createdAt=>Tue, 06 Oct 2020 07:49:00 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
5

ربما أحتاج إلى تشغيل سكريبت لتحويل كل شيء إلى طوابع زمنية

6

ستحتاج إلى تغيير هذا إلى:

.order("COALESCE(CAST(custom_fields.value AS timestamp), custom_fields.value) DESC")

لغرض تحسين قابلية القراءة.

أنت تقوم بالفرز حسب sortDate، وعند عدم وجودها تستخدم createdAt. والكل بترتيب تنازلي. الاستعلام يعيد البيانات تمامًا كما طُلب، حيث أن عام 2020 “أكبر” من المرتبة الثانية عام 2011.

إذا كنت تريد أن تظهر القيم الفارغة في النهاية، ستحتاج إلى:

.order("CAST(custom_fields.value AS timestamp DESC NULLS LAST")
إعجاب واحد (1)
8

عذرًا @Falco، لقد أدركت أن الكود كان صحيحًا وقمت بتعديله بأسرع ما يمكن.

تعديل:
لقد بسّطت كودي إلى:
.order(“coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc”)

فقط لتكون الأمور أكثر أمانًا..

@david بخصوص فكرة التحويل إلى طوابع زمنية. مشكلتي مع ذلك هي أن الفئات لا تملك جميعها نموذج حقول مخصصة للمواضيع نفسه. هذا يعني أنني إما أن أحتاج إلى إنشاء sort_date لكل موضوع، أو إبقاء الأمر كما هو والترتيب بناءً على sort_date || t.created_at.

مع الطوابع الزمنية، يجب أن يكون الأمر شاملًا أو لا شيء على الإطلاق. :frowning:

9

@Falco @david هل لديكم أي فكرة عن تأثيرات الأداء عند استخدام التحويل (casting) في جملة ORDER BY؟

في هذا الموضوع، يقول المؤلف إن الأداء سيتدهور مع زيادة عدد العناصر المُرجعة. أنا لست خبيرًا في SQL، لذا لا أعرف ما إذا كان هذا صحيحًا أم لا. (https://stackoverflow.com/a/491240/4020131)

والسبب في ذلك هو أنه عند استخدام نوع بيانات char، فإنك تقوم بفرز الصفوف كسلسلة نصية.

فكرة استخدام ORDER BY مع CAST() صحيحة، إلا أن أداءها سيتراجع كلما زاد عدد النتائج المُرجعة.

إذا كانت البيانات في هذا العمود رقمية فقط، فإن أفضل ممارسة هي العثور على نوع بيانات رقمي مناسب وتغيير العمود إليه.

وإذا لم تتمكن حقًا من تغيير العمود وواجهت مشاكل في الأداء، فإنني أقترح إضافة عمود لترتيب الفرز يحتوي على القيمة المحولة إلى عدد صحيح (مع تحويل القيم الفارغة null إلى قيمة مناسبة).

قم بفهرسة عمود ترتيب الفرز، والأفضل من ذلك، أضف محفز (trigger) إلى عمود CHAR بحيث يؤدي الإدراج أو التحديث في قيمة char إلى تحديث القيمة الصحيحة.

من خلال ما قاله، أفهم أنه إذا كنت أريد فقط 10-20 موضوعًا، فإن الأداء سيبقى ثابتًا بغض النظر عن عدد المواضيع الموجودة في قاعدة البيانات.

بالنسبة لي، هذا غير بديهي؛ فكيف سيعرف النظام ترتيب جميع المواضيع وإرجاع 10-20 منها إذا لم يقم بالتحويل مسبقًا؟

كما وجدت هذا الموضوع موضوع MSDN، لكنني لا أفهم بالضبط كيف ينطبق على حالتي - أي استخدام CAST في جملة ORDER BY.

10

سيء. إذا كنت تخطط للاستعلام عن هذا في مسار حرج، فمن الأفضل إضافة جدول جديد بنوع عمود مناسب وفهرس في هجرة لإضافتك.

11

ماذا عن ربط جدول الحقول المخصصة، حيث أبحث فقط عن الإدخالات التي تحتوي على topic_id؟ @Falco ألا يؤثر ذلك أيضًا على الأداء؟

12

كنت أفكر للتو… إن الهدف هنا هو ترتيب الأشياء حسب التسلسل الزمني، أليس كذلك؟ بالنظر إلى تنسيق YYYY-MM-DDTHH:MM:SS لتواريخ ISO8601، أعتقد أنه يمكنك فعليًا الاكتفاء بترتيبها “أبجديًا”، وسيظل الترتيب زمنيًا.

بدون التحويل، أعتقد أن postgres يجب أن تكون قادرة على استخدام الفهرس المتاح لدينا على (name, value)، ويجب أن يكون ذلك أكثر كفاءة بكثير.

إعجابَين (2)
13

لكن ماذا عن جزء create_at؟ أود أن يستخدم sortDate عندما يكون متوفراً، وcreated_at عندما لا يكون.

إعجاب واحد (1)
14

انتظر، أليس ISO8601 يُقارن نصياً بشكل صحيح بالفعل؟ أليس هذا جزءاً أساسياً من هدفه؟

نعم.

إعجاب واحد (1)
15

المشكلة هي أنني أريد إجراء فرز مشترك باستخدام sortDate (حيث أملكه) وcreated_at (حيث لا أملكه)، دون فقدان الأداء.

مشكلتي هي أن قيمة الحقل المخصص هي نَص (STRING)، بينما created_at هو نوع تاريخ (DATE).

إعجاب واحد (1)
16

يمكنك تجربة هذا على الأرجح.
https://www.blendo.co/documents/queries-casting-postgresql/#:~:text=The%20TO_DATE%20function%20in%20PostgreSQL,to_timestamp(text%2C%20text).

17

وجدت طريقة للقيام بذلك، لكنها تتضمن التحويل (casting)، إما إلى نوع تاريخ من نص (حيث أن sortDate هي قيمة حقل مخصص كنص) أو تحويل created_at من تاريخ إلى نص.

كنت أبحث عن حل أقل استهلاكًا للموارد ولا يتطلب عملًا إضافيًا مثل نصوص هجرة قاعدة البيانات.

لست متأكدًا من كيفية تعامل منصة Discourse مع الجداول المخصصة من حيث إعادة التأسيس وترقية قاعدة البيانات.

إعجاب واحد (1)
18

سأعود إلى هذا في المستقبل على الأرجح عن طريق ملء حقل sortDate لجميع العناصر. سيكون الحل أنيقًا أكثر لو أمكنني تعيينه على الموضوع نفسه، لكنني لا أعرف مرة أخرى كيف سيؤثر ذلك على ترقيات Discourse.

هل هناك روابط أو نصائح مفيدة حول الجداول المخصصة وكيفية تجنب المشكلات عند وجودها وتحديث Discourse؟

19

الجداول المخصصة أكثر أمانًا بكثير من إضافة أو تعديل الحقول في الجداول الموجودة. لن يتدخل Discourse في جداولك، لكن قد تحتاج إلى بذل بعض الجهد لمواكبة التغييرات في النواة.

يُعد discourse-subscriptions و discourse-calendar أمثلة جيدة على الإضافات الرسمية التي تحتوي على جداول خاصة بها.

إعجابَين (2)
20

@fzngagan لقد بدأت للتو في استخدام Discourse قبل 3-4 أشهر، بالإضافة إلى Ruby :slight_smile:. عندما تقول “قم ببعض العمل”، ما هي الخطوات الدقيقة التي تقصدها؟ هل الأمر يتعلق بكتابة سكريبت هجرة ينشئ الجدول إذا لم يكن موجودًا؟ إذا كان بإمكانك مساعدتي بقائمة صغيرة، فسيكون ذلك مفيدًا جدًا في توفير وقت توثيقي :expressionless:

شكرًا لك مقدمًا

21

إذا لاحظت حدوث أي خلل (وسيتعين عليك التحقق من ذلك بانتظام)، فستضطر إلى إصلاحه باستمرار. بالإضافة إلى ذلك، يمكنك كتابة بعض اختبارات الوحدة التي ستساعدك في تحديد المشكلات بسهولة.

إعجاب واحد (1)