إن أصررت على عدم المبالاة بمسألة عرض السجلات على صفحات مقسمة (تطبيق مبدأ التصفح Paginating)، فالقضية تتعدى خطورتها الانتظار الطويل (الذي قد يكون إلى ما لانهاية) لتنفيذ جملة الاستعلام (هذا إن تم تنفيذها)، وقد تسبب تشتيت مستخدم برنامجك بكثرة السجلات التي ستنفجر أمام أنفه على الشاشة (قد تنال نصيب لا بأس به من دعواته عليك)، وأريدك للحظة تخيل موقع يعرض سجلات بالملايين (كـ yahoo أو google) ولا يطبق مبدأ التصفح Paginating، فستكون مسألة انتظار عرض جميع السجلات في صفحة واحدة قضية تؤدي إلى كارثة في أجهزة الخوادم Servers والعملاء أيضا، ولا اعتقد أن Internet Explorer أو برنامجك سيكون قادر على عرض بيانات 20 مليون سجل في صفحة واحدة!
أحب ان انوه هنا بأني سأضع افتراضا في هذا المقال على اننا نتعامل مع جدولين هما Transactions الذي يمثل العمليات، وCustomers الذي يمثل العملاء، وهذه الحقول:
الكلمة المحجوزة TOP
حتى تتمكن من تحقيق مبدأ التصفح للسجلات، عليك التعرف على أسلحة (ليست محظورة دوليا) تمثل الأدوات التي ستحتاجها للحرب مع عبارة SELECT، السلاح الأول هو الكلمة المحجوزة TOP واستخدامها سهل جدا، فعبارة SELECT التقليدية التالية:
SELECT
*
FROM
[Transactions]
ستعود بجميع السجلات الموجودة في الجدول Transactions، ويمكنك تقليص عدد السجلات وتعود بعدد معين منها بإضافة الكلمة المحجوزة TOP:
SELECT TOP 10
*
FROM
[Transactions]
ما هي العشر سجلات التي ستعرض؟ في الحقيقة لا يمكن التنبؤ بهذه السجلات والسبب أنه لا توجد عبارة للفرز ORDER BY وستكون النتائج غير متوقعة، لذلك يكون الاستخدام الأمثل لهذه الكلمة المحجوزة مع عبارة فرز:
SELECT TOP 10
*
FROM
[Transactions]
ORDER BY
[CreatedDate] DESC
استنادا إلى امر الفرز في الجملة السابقة، سيتم عرض ((أحدث)) عشر سجلات في الجدول بالاعتماد على القيمة في الحقل CreatedDate.
أخيرا، بدلا من استخدام رقم يمثل العدد الصحيح للسجلات التي تود العودة بها، يمكنك SQL Server من الاعتماد على رقم يمثل النسبة المئوية % لعدد السجلات، وذلك عن طريقة الكلمة المحجوزة PERCENT، فهنا نطلب العودة بـ 2% من عدد السجلات:
SELECT TOP 2 PERCENT
*
FROM
[Transactions]
ORDER BY
[CreatedDate] DESC
مع العلم أننا لسنا بحاجة إلى هذه الميزة في هذا المقال.
المعامل IN
بشكل مبدئي، يمكنك المعامل IN من اختصار اوامر الشرط والخاصة بالمعامل المنطقي OR، فالعبارة التالية:
SELECT
*
FROM
[Transactions]
WHERE
[CustomerID] = 1 OR [CustomerID] = 5 OR [CustomerID] = 10
يمكنك اختصارها باستخدام IN:
SELECT
*
FROM
[Transactions]
WHERE
[CustomerID] IN (1, 5, 10)
كما يمكنك استخدام معامل النفي NOT لتقول "لا":
SELECT
*
FROM
[Transactions]
WHERE
[CustomerID] NOT IN (1, 5, 10)
الاستعلامات الفرعية Sub Queries
الاستعلامات الفرعية Sub Queries هي استعلامات بعبارة SELECT داخل عبارة SELECT حاضنة لها، تفيد هذه الاستعلامات كثيرا عند الاحتكاك مع اكثر من جدول. فمثلا، لو أردت الحصول على السجلات من الجدول Transactions بشرط ان يكون راتب العميل (في جدول العملاء Customers) الذي نفذ العملية اكثر من 5000، فيمكنك تحقيق هذه الرغبة بكل سهولة مع استعلام SELECT فرعي:
SELECT
*
FROM
[Transactions]
WHERE
[CustomerID] IN (
SELECT [CustomerID] FROM [Customers] WHERE [Salary] > 5000 )
لاحظ ان عبارة SELECT الفرعية (التي بداخل المعامل IN) تعود بحقل واحد هو [CustomerID]، ولن تستطيع العودة بأكثر من حقل للاستعلام الفرعي ان كان داخل الكلمة المحجوزة IN).
الصفحة رقم 1
حسنا، أعتقد أن لديك ما يكفي من الذخيرة لإعلان الحرب على عبارة SELECT وتطبيق فكرة التصفح Paginating لسجلات جداول قاعدة بياناتك، الحصول على سجلات الصفحة الأولى تعتبر أسهل مهمة، اذ يكفي الاعتماد على الكلمة المحجوزة TOP لتحدد فيها عدد السجلات، في هذا المثال طلبت 10 سجلات (الذي يمثل حجم الصفحة والذي سنعتمد عليه في بقية أمثلة هذا المقال):
SELECT TOP 10
*
FROM
[Transactions]
ORDER BY
[CreatedDate] DESC
جملة الاستعلام السابقة ستعود بالسجلات العشر الأولى من الجدول كما بالشكل التالي (اللون الاصفر يمثل السجلات التي سوف تعود):
يمكنك تغيير جملة الاستعلام السابقة وتحدد فيها الحقول أو تضيف جمل الشرط مع المعامل WHERE (كما تفعل مع عبارات SELECT الأخرى) دون أي مشاكل تذكر:
SELECT TOP 10
[Value], [CreatedDate]
FROM
[Transactions]
WHERE
[Value] > 100
ORDER BY
[CreatedDate] DESC
الصفحة رقم ن
بالفقرة السابقة تمكنا من عرض سجلات الصفحة الأولى، وبهذه الفقرة نحاول ان نجد طريقة تمكنا من الحصول على الصفحة ن (والتي قد تكون الثانية، الثالثة، الرابعة، ...الخ)، الشكل التالي يعرض سجلات الصفحة الثانية باللون الاصفر:
كيف نستطيع ان نحصل على سجلات الصفحة الثانية؟ فلو طبقنا عبارة SELECT في الفقرة السابقة:
SELECT TOP 10
*
FROM
[Transactions]
ORDER BY
[CreatedDate] DESC
ستعود بسجلات الصفحة الاولى كما في هذا الشكل:
وان زدنا عدد السجلات (مع الكلمة المحجوزة TOP) وجعلناها 20 بدلا من 10:
SELECT TOP 20
*
FROM
[Transactions]
ORDER BY
[CreatedDate] DESC
فالمشكلة ان عبارة SELECT السابقة ستعود بالعشرين سجل الأولى (التي تمثل الصفحة الأولى والثانية) كما بهذا الشكل:
ماهو الحل إذا؟ ألا يوجد طريقة أو كلمة محجوزة تتجاهل العشر سجلات الاولى وتظهر الثانية فقط؟
في الحقيقة لا توجد طريقة مباشرة تحقق مبتغاك، والحل يا استاذي الفاضل او يا سيدتي الجميلة هو ان نقوم بحركة ((قذرة)) لنتجاهل فيها العشر سجلات الأولى، وهذه الحركة تتم في خطوتين، الأولى اختيار جميع السجلات العشرون الاولى، والخطوة الثانية تقوم بتجاهل العشر الاولى من العشرين سجل المحددة في الخطوة السابقة، ويتم ذلك باستخدام جملة استعلام فرعية Sub Query اضافية:
SELECT TOP 10
*
FROM
[Transactions]
WHERE
[ID] NOT IN (
SELECT TOP 10 [ID] FROM [Transactions] ORDER BY [CreatedDate] DESC)
ORDER BY
[CreatedDate] DESC
عبارة SELECT الفرعية ستقوم بتحديد السجلات العشر الاولى من الجدول، والغرض منها هو تجاهلها عند تنفيذ عبارة SELECT الرئيسية التي تعود بالعشر سجلات الاولى مع شرط WHERE الذي يمنع ان تكون هذه السجلات ضمن العشر الاولى التي حددت في العبارة SELECT الفرعية، أعلم بأنك لم تفهم شيئا، لذلك ركز معي هنا:
كما ترى بالشكل السابق، فالسجلات العشر الاولى (باللون الرمادي) تم تجاهلها وعادت جملة الاستعلام بالسجلات العشر الثانية فقط (باللون الاصفر)، والسبب ان جملة الاستعلام الفرعية قد حددت السجلات العشر الاولى، وعندما جاء وقت تنفيذ جملة الاستعلام الرئيسية (الحاضنة للفرعية)، وضعنا شرطا يمنع ظهور السجلات التي تم الحصول عليها في جملة الاستعلام الفرعية (ان لم تفهم هذه المرة، فالمعذرة لاني لا استطيع ان اقدم لك اكثر من هذا).
اما ان اردنا الحصول على سجلات الصفحة الثالثة، فنستطيع تطبيق نفس السيناريو ولكن سنطلب منه هذه المرة تجاهل العشرين سجل الأولى وعرض العشر سجلات الثالثة كما بالشكل التالي:
وهذا نص جملة الاستعلام:
SELECT TOP 10
*
FROM
[Transactions]
WHERE
[ID] NOT IN (
SELECT TOP 20 [ID] FROM [Transactions] ORDER BY [CreatedDate] Desc)
ORDER BY
[CreatedDate] DESC
ألا يوجد قاعدة نسير عليها؟
بكل تأكيد يوجد قاعدة نسير عليها (وان لم توجد لما استعرضت عضلاتي في هذا المقال)، فلو دققت النظر في امثلة الفقرة السابقة، نلاحظ ان عملية تجاهل السجلات (في عبارة SELECT الفرعية) تمثل (عدد الصفحات x حجم الصفحة) – حجم الصفحة، مما يعني انه يمكن تعريف هذا القانون:
SELECT TOP pageSize
*
FROM
[Transactions]
WHERE
[ID] NOT IN (
SELECT TOP (pageSize x pageNum) – pageSize [ID] FROM
[Transactions] ORDER BY [CreatedDate] Desc)
ORDER BY
[CreatedDate] DESC
حيث يمثل pageSize حجم الصفحة و pageNum رقم الصفحة، فلو أردنا عرض سجلات الصفحة العاشرة، قد تكتب شيئا مثل:
SELECT TOP 10
*
FROM
[Transactions]
WHERE
[ID] NOT IN (
SELECT TOP 90 [ID] FROM [Transactions] ORDER BY [CreatedDate] Desc)
ORDER BY
[CreatedDate] DESC
الصفحة الأخيرة
بخصوص الصفحة الأخيرة، فلديك خيارين (ليس مثنى خيار للأكل وانما اختيار)، الأول (وهو المفضل بالنسبة لي) الاستمرار في تطبيق القاعدة السابقة، ولكن عليك معرفة عدد السجلات حتى تحدد رقم الصفحة (التي تمثل الصفحة الاخيرة) لتطبقها في المعادلة السابقة.
عند تطبيق القاعدة ووضعت رقم صفحة تزيد عن عدد السجلات، فلا توجد مشكلة ولن تظهر أخطاء وقت تنفيذ Run time errors وسيعود الاستعلام بلاشيء.
اما الطريقة الاخرى (وهي غير مفضلة بالنسبة لي) هي تطبيق نفس جملة الاستعلام للحصول على الصفحة الاولى ولكن ((عكس)) جمل الفرز (في عبارة ORDER BY):
SELECT TOP 10
*
FROM
[Transactions]
ORDER BY
[CreatedDate] DESC
لاحظ اني قمت بحذف الكلمة المحجوزة DESC حتى نجعل الترتيب تصاعدي Ascending بدلا من تنازلي Descending. والسبب الذي يجعل هذه الطريقة غير مفضلة (بالنسبة لي)، أنه في حالات الاستعلامات المعقدة والتي وضعت نظام فرز معقد خاص بها، علي تغيير وعكس كل عملية فرز.
كلامك جميل، ولكني بحاجة الى شرط اضافي
واقعيا، من النادر جدا كتابة جملة استعلام SELECT بدون شرط باستخدام WHERE. إن كانت متطلبات برنامجك تحتم عليك اضافة شرط WHERE اضافي، لا تنسى ان تضع هذا الشرط ضمن كلا عبارتي SELECT (الرئيسية والفرعية):
SELECT TOP 10
*
FROM
[Transactions]
WHERE
[Value] > 100
AND [ID] NOT IN (
SELECT TOP 10 [ID] FROM [Transactions] WHERE
[Value] > 100 ORDER BY [CreatedDate] DESC)
ORDER BY
[CreatedDate] DESC
وماذا عن الدمج JOIN ؟
قد تحتاج -لحاجة ما في نفس يعقوب- من التحرش بجدول اخر في قاعدة البيانات لتطبيق مبدأ الدمج Joining مع حقول جدول آخر باستخدام الأمر JOIN. منطقيا، يكفي البدء في عملية الدمج عند تنفيذ عبارة SELECT الرئيسية، ولا يوجد أي داعي من اضافتها في عبارة SELECT الفرعية حتى لا تضعف كفاءة التنفيذ، هنا مثال لدمج جدول العملاء Customers:
SELECT TOP 10
*
FROM
[Transactions]
INNER JOIN
[Customers] ON [CustomerID] = [ID]
WHERE
[ID] NOT IN (
SELECT TOP 10 [ID] FROM [Transactions] ORDER BY [CreatedDate] DESC)
ORDER BY
[CreatedDate] DESC
خاص بمبرمجي ADO.NET
قد تقابل شخص من مبرمجي ADO.NET المحترفين وينصحك بعدم إضاعة وقتك وتطبيق ما نص عليه هذا المقال، فالكائن SqlClient.SqlDataAdapter يوفر ميكانيكية لتحديد عدد الصفحات التي تود اضافتها في كائن DataSet، هنا مثلا نضيف العشر سجلات الاولى فقط:
Dim da As SqlClient.SqlDataAdapter
Dim ds As Data.DataSet
…
…
…
da.Fill(ds, 0, 10, "Transactions")
وهنا العشر سجلات الثانية:
da.Fill(ds, 10, 10, "Transactions")
في الحقيقة والواقع (ومع فائق تقديري واحترامي لمبرمجي ADO.NET)، ما قاموا به ليس سوى ضحكا على الذقون! فالكود السابق سيعود ((بكامل)) السجلات من خادم SQL Server (التي قد تصل إلى الملايين)، وبعد ان تعطل الجميع في خطوط الشبكة وتزاحم الطلبات الاخرى (ولا زحمة طريق الملك فهد بعز الظهيرة) وتصل بالسلامة (هذا إن وصلت بالسلامة)، يقوم الكود بقراءة عدد معين منها ومن ثم نسخها إلى الكائن DataSet، والسجلات المتبقية حجزت أماكن بالذاكرة (على الفاضي) دون الاستفادة منها (بالعربي، تضحك على نفسك!!!)
كما انه توجد مشكلة اخرى وهي في حالة عدم اعتمادك على كائن محول SqlClient.SqlDataAdapter في برنامجك، فلو أردت استخدام كائن SqlClient.SqlDataReader (للوضع المتصل Connected Mode مع قاعدة البيانات)، فلا توجد به طريقة لتحديد عدد السجلات. لذلك، كن معي وابتعد عن رفقاء السوء!
إن لم تفهم شيئا
حتى إن لم تفهم شيئا من هذا المقال، وكانت جمل الاستعلام SQL معقدة بعض الشيء فلا تقلق، فهناك هدية مني لك تساعدك في توليد اوامر جمل الاستعلام دون ان تطلب منك فهم الحركات القذرة التي فعلتها هنا. الكود التالي يمثل دالة GetPagesSQL صممتها بلغة Visual Basic .NET تعود بقيمة من نوع String تعود بعبارة SQL خاصة للتصفح:
تتوقع هذه الدالة 8 بارامترات ترسلها عند استدعائها وهي:
| pageNum |
رقم الصفحة. |
| pageSize |
حجم الصفحة (عدد السجلات للصفحة الواحدة). |
| pKey |
المفتاح الابتدائي للجدول Primary Key. |
| tableName |
اسم الجدول. |
| selectedFields |
الحقول التي تود العودة بها (ارسل "*" للعودة بجميع الحقول). |
| ordering |
اسلوب الفرز Sorting الذي تود تطبيقه (مثال: “[CreatedDate] DESC” ). |
| whereCluase |
اختياري، جملة الشرط الاضافية التي تود تطبيقها (مثال: “[Value] > 100” ). |
| joinStatment |
اختياري، جملة الدمج JOIN التي تود تطبيقها. (مثال: “JOIN [Customers] ON [CustomerID] = [ID]” ). |
والان ليس لديك أي حجة في عدم تطبيق مبدأ التصفح Paginating!
خاتمة
عرض سجلات قاعدة البيانات على شكل صفحات مقسمة مطلب ضروري لتحسين كفاءة التنفيذ وتسهيل عملية قرائتها على المستخدم (خاصة ان زاد عدد السجلات)، وكل ما يلزمك استخدام الكلمة المحجوزة TOP والاستعلامات الفرعية Sub Queries بشكل ذكي حتى تتمكن من تطبيق هذا المبدأ في مشاريعك القادمة.
-- تركي