dimanche 28 juin 2015

Store and retrieve user favorites in mysql

I have an android app that pulls information from mysql db and displays it in a list. Each list item has a favorite button that upon click updates the db with the username and with the list item.

I can get all user favorites using relational and foreign key. What I am trying to achieve is when the user re-logs in to the App and scrolls for available items he can already see which items he marked as favorite without going to his favorites page.

So for example when you are scrolling in your facebook page you can already see which posts you liked.

How can I achieve that?

First table

|------------------|--------------|----|-------|
|      Column      |     Type     |Null|Default
|------------------|--------------|----|-------|
|    //**id**//    |   int(11)    | No |
|    car_vendor    |  varchar(20) | No |
|    car_model     |  varchar(20) | No |
|    car_petro     |  varchar(10) | No |
| car_transmition  |  varchar(10) | No |
|     car_hand     |   int(11)    | No |
|  car_spedometer  |  varchar(7)  | No | 
|  car_engine_vol  |  varchar(4)  | No |
|   car_category   |   int(11)    | No |
|  car_post_date   |     date     | No |

second table

|----------------|------------|----|
|     Column     |    Type    |Null| Default
|----------------|------------|----|
|   //**id**//   |   int(11)  | No |
|    username    | varchar(20)| No |
|  favorites_id  |   int(11)  | No | <<< foreign key points to ID on first table 

Writing a query to find the following result

Employees Table:

EmpID EmpName


1 John Torres

2 Irina Williams

Payroll Week Table:

WeekID EmpID WeekStart WeekEnd


1 1 11-20-2011 11-26-2011

2 2 11-27-2011 12-03-2011

3 1 11-27-2011 12-03-2011

Employee Visits Table:

ID EmpID VisitDate StartTime EndTime Earningcode


1 1 11-20-2011 10:00 12:00 Sat-Sun1

2 1 11-21-2011 13:30 16:00 Mon-Fri1

3 1 11-22-2011 14:00 15:00 Mon-Fri1

4 1 11-24-2011 10:00 14:00 Mon-Fri1

5 1 11-25-2011 13:30 16:00 Mon-Fri1

6 1 11-26-2011 14:00 15:00 Sat-Sun1

7 2 11-27-2011 09:00 11:00 Sat-Sun1

8 2 11-28-2011 07:00 12:00 Mon-Fri1

9 2 11-29-2011 09:00 11:00 Mon-Fri1

10 2 12-03-2011 07:00 12:00 Sat-Sun1

Expected Results

RecordType EmpID EmpName WeekStart WeekEnd Earning code Hours


H 1 John Torres 11-20-2011 11-26-2011

D Sat-Sun1 3.00

D Mon-Fri1 10.00

H 2 Irina Williams 11-27-2011 12-03-2011

D Sat-Sun1 7.00

D Mon-Fri1 7.00

Sql Error when running queires file

I am trying to run CREATE TABLE persons (id int(4) NOT NULL auto_increment, name varchar(30) NOT NULL, favorite_color varchar(30) NOT NULL, weight int(5) NOT NULL, favorite_movie varchar(20), state varchar(20), PRIMARY KEY (id));

I am recieving syntax error near unexpected token `('

Any help appreciated what im doing wrong.

HQL query to get min id

This query returns the number of places available for the same table. Of all the id table returned I need one with smaller IDs. The id of the table is a string.

String SQL_QUERY = "   FROM PostoDBWrapper p"
                +    " WHERE p.tavolo  in"
        + "                     (SELECT p.tavolo "
        + "                     FROM PostoDBWrapper p "
+ "                             WHERE p.disponibilita = true  "
+ "                             GROUP BY p.tavolo             "
+ "                             HAVING COUNT(p.tavolo) >= :posRichiesti)"
+ "                    AND p.disponibilita = true" ;


Query query = session.createQuery(SQL_QUERY);  
query.setParameter("posRichiesti",posRichiesti);  

SELECT column WHERE time_type = 'Break' but only the rows that are after(below) time_type = 'Start'

currently I'm stuck in an issue, hope some good PostgreSQL fellow programmer could give me a hand with it. This is my table...

enter image description here

I would like to SELECT all 'time_elapse' WHERE time_type = 'Break' but only the rows that are after(below) the last(descendent) time_type = 'Start' and sum them up.

So in the table above I would SELECT...

time_elapse           |   time_type  |  time_index
----------------------+--------------+-------------
00-00-00 01:00:00.00  |   Break      |  2.1
00-00-00 01:00:00.00  |   Break      |  2.2

So totalbreak = 00-00-00 02:00:00.000

I know how to convert character varying to timestamp in order to sum them up (please don't bother with that, I'm not looking for help with that, let's image 'time' and 'time_elapse' columns are proper timestamps), I just don't know how would be the syntax to select all possible 'Breaks' and sum them up (lets say the max Breaks between each 'Start' is nine).

I can hardly imagine a way to do that, so I would like to ask for suggestions.

Troubles with using GROUP BY in SQL Query

I'm trying to write a propper SQL query in MS SQL Server. First of all, i have the following tables: Towns, Employees, Addresses. Almost every employee has Manager, whom ManagerID is foreign key in Employees also. (Self relation). My goal is to display the number of managers from each town. So far i have this code:

SELECT t.Name, COUNT(*) AS [Managers from each town] 
FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
GROUP BY t.Name
ORDER BY [Managers from each town] DESC

This query returns the number of Employees, from each town, not Managers. If i try the second query bellow, I get something totally wrong:

SELECT t.Name, COUNT(*) AS [Managers from each town] 
FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
JOIN Employees m
ON e.ManagerID = m.ManagerID

GROUP BY t.Name
ORDER BY [Managers from each town] DESC

Here is the structure of 'Employees' table:

EmployeeID, FirstName, LastName, MiddleName, JobTitle,DepartamentID, ManagerID, HireDate, Salary, AddressID

The correct query must return this result set:

Town          | Managers from each town
Issaquah      | 3
Kenmore       | 5
Monroe        | 2
Newport Hills | 1

Avoid Duplicate values for INSERT in SQL

  <?php 
mysql_query("INSERT INTO `users_badges` (`user_id`, `badge_id`, `badge_slot`) VALUES ('$my_id', 'VIP', '1')");
?>

I have this code to insert something in the table but every time if you visit the home page the same user gets the item dubplicated. How can I fix this.

So basicilly I want IF already you have VIP,the code doesn't have a value to avoid duplicate.

mysql query optimiser better performance from subquery with partitions than left join with index

when using mysql version 5.6.14-enterprise-commercial-advanced-log on an ubuntu 12.04 LTS, I encounter the following behaviour when querying data from these tables:

CREATE TABLE `a` (
          `id` varchar(32) DEFAULT NULL,
          `request_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1
        !50100 PARTITION BY RANGE (UNIX_TIMESTAMP(request_time))
        (PARTITION p15062116 VALUES LESS THAN (1434895200) ENGINE = MyISAM,
         PARTITION p15062117 VALUES LESS THAN (1434898800) ENGINE = MyISAM,
         PARTITION p15062118 VALUES LESS THAN (1434902400) ENGINE = MyISAM,
        ...
        PARTITION rest VALUES LESS THAN MAXVALUE ENGINE = MyISAM)

CREATE TABLE `b` (
          `id` varchar(50) NOT NULL,
          `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `item` int(11) DEFAULT NULL,
          `item2` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`,`start_time`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8
        !50100 PARTITION BY RANGE (UNIX_TIMESTAMP(start_time))
        (PARTITION p15062516 VALUES LESS THAN (1435240800) ENGINE = MyISAM,
         PARTITION p15062517 VALUES LESS THAN (1435244400) ENGINE = MyISAM
        ....
        PARTITION rest VALUES LESS THAN MAXVALUE ENGINE = MyISAM)

using this query results in a 1 second runtime:

SELECT SQL_NO_CACHE count(*)  FROM a left join (select * from b where start_time between '2015-06-28 10:00:00' and '2015-06-28 11:00:00') c using(id) where request_time between '2015-06-28 10:00:00' and '2015-06-28 11:00:00';

explain output:

+----+-------------+------------+------+---------------+-------------+---------+------+--------+-------------+
| id | select_type | table      | type | possible_keys | key         | key_len | ref  | rows   | Extra       |
+----+-------------+------------+------+---------------+-------------+---------+------+--------+-------------+
|  1 | PRIMARY     | a   | ALL  | NULL          | NULL        | NULL    | NULL | 336972 | Using where |
|  1 | PRIMARY     | <derived2> | ref  | <auto_key0>   | <auto_key0> | 152     | func |     10 | Using where |
|  2 | DERIVED     | b  | ALL  | NULL          | NULL        | NULL    | NULL |  39508 | Using where |
+----+-------------+------------+------+---------------+-------------+---------+------+--------+-------------+
3 rows in set (0.00 sec)

and using this query results in 30 seconds runtime:

SELECT SQL_NO_CACHE count(*)  FROM a  left join b using(id) where request_time between '2015-06-28 10:00:00' and '2015-06-28 11:00:00' and start_time between '2015-06-28 10:00:00' and '2015-06-28 11:00:00';

explain output:

+----+-------------+-----------+------+---------------+---------+---------+------+--------+--------------------------+
| id | select_type | table     | type | possible_keys | key     | key_len | ref  | rows   | Extra                    |
+----+-------------+-----------+------+---------------+---------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | a  | ALL  | NULL          | NULL    | NULL    | NULL | 336972 | Using where              |
|  1 | SIMPLE      | b | ref  | PRIMARY       | PRIMARY | 152     | func |    395 | Using where; Using index |
+----+-------------+-----------+------+---------------+---------+---------+------+--------+--------------------------+
2 rows in set (12.17 sec)

I expected similar or better results from the second query based on the usage of the id index and the 1 hour partition. both tables have 1000000~ records each.

can you explain why is the first query much more efficient than the second one?

can we refactor the first query so that it might become a view or a reusable query instead of rebuilding the subquery for each join?

thanks

Why does MAX(Column) as Column give error, and MAX(Column) as max_Column does not?

I was just wondering, why does:

select max(run_id) as run_id from my_table where run_id > 50;

It gives an error and

select max(run_id) as max_run_id from my_table where run_id > 50;

select max(run_id) from my_table where run_id > 50;

the above two queries does not give an error.

Let's say the structure of the table is,

create table my_table(
run_id int,
something varchar(10))

This table has 100 run_id's.

I know you can't use where clause with aggregate functions.

Is it because we rename the column (as max_run_id) and the sql is treating it as a separate column, where if the name was the same as the original column it sees the aggregate function and gives the error because of it? Or can someone explain that with better terms.

insert from multiple tables into other

This is the table structure for the seven tables I'm trying to join into just one:

-- tables: en, fr, de, zh_cn, es, ru, pt_br
`geoname_id` INT (11),
`continent_code` VARCHAR (200),
`continent_name` VARCHAR (200),
`country_iso_code` VARCHAR (200),
`country_name` VARCHAR (200),
`subdivision_1_name` VARCHAR (200),
`subdivision_2_name` VARCHAR (200),
`city_name` VARCHAR (200),
`time_zone` VARCHAR (200)

And this is the new table structure, where all data will be stored:

CREATE TABLE `geo_lists` (
    `city_id` int (11), -- en.geoname_id (same for all 7 tables)
    `continent_code` varchar (2), -- en.continent_code (same for all 7 tables)
    `continent_name` varchar (200), -- en.continent_name (just in english)
    `country_code` varchar (2), -- en.country_iso_code (same for all 7 tables)
    `en_country_name` varchar (200), -- en.country_name
    `fr_country_name` varchar (200), -- fr.country_name
    `de_country_name` varchar (200), -- de.country_name
    `zh_country_name` varchar (200), -- zh_cn.country_name
    `es_country_name` varchar (200), -- es.country_name
    `ru_country_name` varchar (200), -- ru.country_name
    `pt_country_name` varchar (200), -- pt_br.country_name
    `en_state_name` varchar (200), -- en.subdivision_1_name
    `fr_state_name` varchar (200), -- fr.subdivision_1_name
    `de_state_name` varchar (200), -- de.subdivision_1_name
    `zh_state_name` varchar (200), -- zh_cn.subdivision_1_name
    `es_state_name` varchar (200), -- es.subdivision_1_name
    `ru_state_name` varchar (200), -- ru.subdivision_1_name
    `pt_state_name` varchar (200), -- pt_br.subdivision_1_name
    `en_province_name` varchar (200), -- en.subdivision_2_name
    `fr_province_name` varchar (200), -- fr.subdivision_2_name
    `de_province_name` varchar (200), -- de.subdivision_2_name
    `zh_province_name` varchar (200), -- zh_cn.subdivision_2_name
    `es_province_name` varchar (200), -- es.subdivision_2_name
    `ru_province_name` varchar (200), -- ru.subdivision_2_name
    `pt_province_name` varchar (200), -- pt_br.subdivision_2_name
    `en_city_name` varchar (200), -- en.city_name
    `fr_city_name` varchar (200), -- fr.city_name
    `de_city_name` varchar (200), -- de.city_name
    `zh_city_name` varchar (200), -- zh_cn.city_name
    `es_city_name` varchar (200), -- es.city_name
    `ru_city_name` varchar (200), -- ru.city_name
    `pt_city_name` varchar (200), -- pt_br.city_name
    `time_zone` varchar (30) -- en.time_zone (same for all 7 tables)
);

I'd like to join them all, using the locale (language) code as prefix for the column names.

SQL query with AND OR operator

I have this sql query

SELECT entries.*, categories.name as name_cat, areas.name as name_area, sub_categories.name as name_sub
FROM entries, areas, sub_categories, categories
WHERE sub_categories.id= entries.id_sub
  AND **areas.id = entries.area**
  AND categories.id = entries.id_cat
ORDER BY entries.id DESC

It works good but i do have some BLANK fields in entries.area .. which is not coming in result..

I try few changes in query but some give me result while some give less nothing gives me proper Answer

How to use partition to select only the desired row?

Here is my table:

table1:
id_nbr                 op_nbr
123E0100               HTM
123E0102               FO
145E0102               HTM
145E0104               FO
198E0100               HTM
234E0100               JAV
234E0102               FO

123E0102, 145E0104 are the id_nbrs I need selected.

For both of them, there exists an id_nbr that matches on the first 6 characters. Op_nbr associated with the originl ID_nbrs are HTM. Last 2 characters increment by 2.

Give me all rows where first 6 characters are equal and last 2 characters is 02 higher than the row with op_nbr HTM associated.

Any help or input is appreciated. Thank You

Beginner learning sql joins in mySQL

I have created 3 tables: dog, customer and owner. It is a many to one relationship with many dogs owned by 1 customer.

CREATE TABLE dog
(
DogID int(6) NOT NULL,
DogName varchar(15),
medicalID int (6),
Gender character(1) check(gender in ('m', 'f')),
Age int(2),
Breed varchar(15),
size character (1) check(size in ('s', 'm', 'l')),
primary key (DogID));

CREATE TABLE Owns
(
DogID int (6)primary key,
CustomerID int (6),
foreign key (CustomerID) references customer (CustomerID),
foreign key (DogID) references dog (DogID));

CREATE TABLE customer
(
CustomerID int(6) NOT NULL,
FirstName varchar(15),
Surname varchar(15),
Address varchar(225),
email varchar (30), 
TelNo varchar (15),
MobNo Varchar (15), 
EmergencyName varchar (40), 
EmergencyPhoneNo varchar(15),
primary key (CustomerID));

I have queried with but nothing is returned.

select dog.dogName 
from dog, owns, customer
where owns.dogID = dog.dogId
  and customer.customerID = owns.customerID
  and customer.FirstName = "CustomersName";

Remove duplicates in MySQL table - set group_id when city_id is the same

I have table units in my database. In schema I have fields id, unit_id, group_id, city_id.

For simple I have 3 units:

(1, 1, 1, 1)
(2, 1, 2, 1)
(3, 1, 3, 2)

How can I remove useless groups id, when city id is the same. I have next result:

(1, 1, 1, 1)
(2, 1, 1, 1)
(3, 1, 3, 2)

I know how do this in PHP, but I think 'maybe MySQL has inbuild functions which i don't know' ;)

Regards

how to return more than 1 row in PostgreSQL function?

Basicly my quesion is how to do this How to return more than 1 record? in PostgreSQL.

I have a query which returns 80 rows, and I want to put it in a function.

I defined a type:

CREATE TYPE typ_new AS
   (id integer,
    pn text,
    name citext,
    qty numeric,);

and I wrote a function :

CREATE OR REPLACE FUNCTION stock.func()
  RETURNS typ_new AS
  $BODY$
  begin

            QUERY

end;
 $BODY$
 LANGUAGE plpgsql VOLATILE

but it returns only 1 row instead of 80. I know I should do some sort of loop because I need to add each row to the return type but I just can find the proper syntax to do that.

I'm trying to do what is written here under 39.6.1.2. RETURN NEXT and RETURN QUERY but I don't understand what is foo%rowtype , I know it's 99% of the answer but I just can't make it work...

How to change the SqlStatementSource in a SSIS package through job step advanced tab

I have a ssis package deployed and created a sql agent job which executes the package.I need to change the SqlStatementSource in one of the sql task in package through job step advanced tab. Can any one help me how to do that? I somewhere read its possible but not able to recall how exactly it can be done?

Login with EJB sessionbean and servlet

I am trying to build a login stateless session bean that i will be calling from my servlet.The query selects ACCESS(boolean value) from the user table and if the value is true access will be granted. if false it means account is deactivated and if the resultlist is empty user shout get failed login. am just having trouble implementing it.

package Dao;

import Model.Account;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

@Stateless
public class AccountAccessDao implements AccountAccessDaoLocal {
    boolean status=false;

    @PersistenceContext
    private EntityManager emlogin;

    @Override
    public boolean accountLogin(String username,String password) {
    Query query = emlogin.createNativeQuery("SELECT e.access FROM Account e WHERE e.username='" + username + "'AND e.password='" + password + "'",Account.class);

    return query.getResultList().contains(this);
}
}








package Controller;

import Dao.AccountAccessDaoLocal;
import java.io.IOException;
import static java.lang.System.out;
import javax.ejb.EJB;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class AccessServlet extends HttpServlet {
@EJB
    private AccountAccessDaoLocal userlogin;

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {

            String username = request.getParameter("username");
            String password = request.getParameter("password");

            boolean status = userlogin.accountLogin(username, password);

            if(status==true){
                out.println("Success");
                //Accout confirmation page
            }else if(status==false){
                out.println("account locked");
                //Accout confirmation page
            }else {
                out.println("Failure");
                //failure page here
            }
 } }

Writing a query to display the following result?

There are 3 tables

Table A:
code        aname
------- ----------
1           A
2           B
3           C


Table B:
code        bname
----------- ----------
1           aaa
1           bbb
2           ccc
2           ddd


Table C
code        cname
----------- ----------
1           xxx
1           yyy
1           zzz
2           www

We need to write a query that would display the following result.

code        aname      bname            cname
----------- ---------- ----------      ----------
1                  A    aaa              xxx
1                  A    bbb              yyy
1                  A    NULL             zzz
2                  B    ccc              www
2                  B    ddd              NULL
3                  C    NULL             NULL

How to speed up the query in PostgreSQL

I have DB in PostgreSQL with a big data(now it is somewhere about 46 GB and db will continue to grow). I create index on often used column. Config file change too - shared_buffers = 1GB, temp_buffers = 256MB, work_mem = 512MB.. But query answered very slowly. Query example:

select distinct us_category_id as cat, count(h_user_id) as res from web_hits 
inner join users on h_user_id = us_id 
where (h_datetime)::date = ('2015-06-26')::date and us_category_id != ''
group by us_category_id

Explain Analyse:

HashAggregate (cost=2870958.72..2870958.93 rows=21 width=9) (actual time=899141.683..899141.683 rows=0 loops=1)

Group Key: users.us_category_id, count(web_hits.h_user_id)

-> HashAggregate (cost=2870958.41..2870958.62 rows=21 width=9) (actual time=899141.681..899141.681 rows=0 loops=1)

Group Key: users.us_category_id

-> Hash Join (cost=5974.98..2869632.11 rows=265259 width=9) (actual time=899141.679..899141.679 rows=0 loops=1)

Hash Cond: ((web_hits.h_user_id)::text = (users.us_id)::text)

-> Seq Scan on web_hits (cost=0.00..2857563.80 rows=275260 width=7) (actual time=899141.676..899141.676 rows=0 loops=1)

-> Seq Scan on web_hits (cost=0.00..2857563.80 rows=275260 width=7) (actual time=899141.676..899141.676 rows=0 loops=1)

Filter: ((h_datetime)::date = '2015-06-26'::date)

Rows Removed by Filter: 55051918

-> Hash (cost=4292.99..4292.99 rows=134559 width=10) (never executed)

-> Seq Scan on users (cost=0.00..4292.99 rows=134559 width=10) (never executed)

Filter: ((us_category_id)::text <> ''::text)

"Planning time: 1.309 ms" "Execution time: 899141.789 ms"

Date is changed. How can I speed up the query?

how to make Wildcards consider spaces between

I'm working with a table with three columns( ID | NOTE |TAG)

ID- primary key

NOTE- some things

TAG- contains values separated with space

imagine that id-1 contains TAG- computer javaScript java forton cellphone

and id-2 contains TAG- computer java forton c++ android

When using a Wildcard to search for term java which is in middle of a string the SQLITE also shows the row containing javascript

My query " LIKE '%" + "java" + "%'"; this shows the rows if the word java is found anywhere inside (i.e) it shows java as well as javaScript as well as Scriptinjava

how to make it show only java ??

SQL Server Database recovery from corrupt database

Last week I backed up my SQL Server by using Backup Exec 2012. I named the file "SQL Server BAK" which contained copies of my SQL Server databases. A few days ago I lost some part of my data due to accidental deletion. I backed it up, so I tried to restore the database from the .bkf file. The problem comes here, when I try to to restore my .bkf file, it becomes inaccessible.

Does anyone know what causes this? I'm suspecting corruption here (it's just a suspicion as I'm not sure). Please help me. The copies of my databases are very crucial.

Many thanks in advance.

Combining 2 queries - getting column names in one and using results in another query

Building my first MS Access SQL queries. That should not be this hard!
I have 2 tables:

Data table AccessRights table

A user belonging to GroupA logged in. I want to show him only those Data table rows and columns which GroupA is assigned to, like this:

╔════════╦════════╦════════╗
║ Group  ║  Data3 ║ Data4  ║
╠════════╬════════╬════════╣
║ GroupA ║   9    ║   4    ║ 
╠════════╬════════╬════════╣
║ GroupA ║   1    ║   5    ║
╚════════╩════════╩════════╝

I tried this silly option:

SELECT (select Data from AccessRights where GroupA = "y")
FROM Data
WHERE Data.Group = "GroupA";

SQL Server float data type understanding

http://ift.tt/1fY2rQ0

After I insert a value (0.12346789123456789123456789) for example in the table that has a float type column, I query and get back 0.1234567891234568 which contains 17 digits. I have 3 questions

  1. How can I back track the binary representation of the input and output ? The document says it uses 53 bits as default. I am using Management Studio SQL Server and I don't know how to specify n value during declaration of my column type.
  2. The number 17 isn't included in the document, I wish to know where it comes from.
  3. In Big or Little Endian systems, I'd like to know how such an input is treated and translated into the output at the low-level byte system. If anyone knows an explanation, I would be thankful.

Does order matter in SQL?

Let's say I have to run the SQL query:

SELECT data FROM table WHERE condition1 AND condition2 AND condition3 AND condition4 

Is that any different than

SELECT data FROM table WHERE condition3 AND condition1 AND condition4 AND condition2

?


If it's not different:

I know from my own experience that condition1 is less expensive than condition2 is less expensive than condition3 is less expensive than condition4.

If any of the prior conditions are not met, the remaining conditions should never be checked. It wouldn't be immediately obvious to the optimizer, as stored functions are involved. How should I write a query that does this?

Check whether particular name order is available in my table

I have the following table stops how can I check whether the following stops name order GHI, JKL, MNO is available in my stops table?

stops table:

CREATE TABLE IF NOT EXISTS stops
(
  stop_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  name varchar(30) NOT NULL, 
  lat double(10,6), 
  longi double(10,6)
);

Simple:

1 ABC
2 DEF
3 GHI
4 JKL
5 MNO
6 PQR
7 SDU
8 VWX

Get SUM() from mysql JOIN query

I am trying to get a sum(vote_amount) from a joined table 'votes' in this mysql query. Unfortunately it gives me an error. How do I get this query to work?

SELECT d.discussion_id, d.discussion_google_id, d.discussion_title, d.discussion_text, d.discussion_views, d.discussion_last_view_ip, d.discussion_created, d.discussion_updated, u.google_picture_link, v.sum(vote_amount)
FROM discussions AS d
INNER JOIN google_users AS u
ON u.google_id = d.discussion_google_id
INNER JOIN votes AS v
ON v.vote_discussion_id = d.discussion_id
WHERE d.discussion_deleted = 0

The error it gives is:

#1630 - FUNCTION v.sum does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

can i have an alias for the result table of inner join

Can I have an alias of the result of the join of 2 tables? So that I can call the result of the table in its alias name.

SELECT emp_table.Name, dept_table.dept
FROM dept_table
inner join emp_table
on emp_table.dept_id = dept_table.dept_id;

How do I loop thought each DB field to see if range is correct

I have this response in soapUI:

 <pointsCriteria>
    <calculatorLabel>Have you registered for inContact, signed up for marketing news from FNB/RMB Private Bank, updated your contact details and chosen to receive your statements</calculatorLabel>
    <description>Be registered for inContact, allow us to communicate with you (i.e. update your marketing consent to 'Yes'), receive your statements via email and keep your contact information up to date</description>
    <grades>
       <points>0</points>
       <value>No</value>
    </grades>
    <grades>
       <points>1000</points>
       <value>Yes</value>
    </grades>
    <label>Marketing consent given and Online Contact details updated in last 12 months</label>
    <name>c21_mrktng_cnsnt_cntct_cmb_point</name>
 </pointsCriteria>

There are many many many pointsCriteria and I use the below xquery to give me the DB value and Range of what that field is meant to be:

<return>
{
for $x in //pointsCriteria
return <DBRange>
<db>{data($x/name/text())}</db> 
<points>{data($x//points/text())}</points>
</DBRange>
}
</return>

And i get the below response

<return><DBRange><db>c21_mrktng_cnsnt_cntct_cmb_point</db><points>0 1000</points></DBRange>

That last bit sits in a property transfer. I need SQL to bring back all rows where that DB field is not in that points range (field can only be 0 or 1000 in this case), my problem is I dont know how to loop through each DBRange/DBrange in this manner? please help

How can compare between times in while loop in stored procedure?

Please help me on this, I have been working to do conditions to get the time within the 24 hours only.

So I have something like this. Can anyone help me or tell me if the conditions is correct.

my @DisplayStartTime = 11:00PM

Basically my @nextStartTime = 11:00PM

OPEN cPhosLineTimeSlot

FETCH NEXT FROM cPhosLineTimeSlot
      INTO @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance 

WHILE @@FETCH_STATUS = 0
BEGIN
    Declare @nextStartTime nvarchar(30)
    set @nextStartTime = @DisplayStartTime

    --insert into #ActualTimeSlot
    --select @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance 

    WHILE (convert(varchar, convert(time, @nextStartTime), 100) < DATEADD(day, -1, GETDATE()))
    BEGIN
        SET @DisplayStartTime = @nextStartTime

        SELECT @nextStartTime = ltrim(right(convert(nvarchar(100), DATEADD(minute, @Frequency, @nextStartTime)), 8))

        INSERT INTO #ActualTimeSlot
            SELECT @Parameter, @DisplayStartTime, 
                   @nextStartTime, @CodeEndTime, @Frequency, @Tolerance 


        SET @intFlag +=1
    END 

    SET @intFlag = 1

    FETCH NEXT FROM cPhosLineTimeSlot
        INTO @Parameter, @DisplayStartTime, @DisplayEndTime, @CodeEndTime, @Frequency, @Tolerance 
END

CLOSE cPhosLineTimeSlot
DEALLOCATE cPhosLineTimeSlot

This is the sample:

enter image description here

Create a view of an account showing hierarchy of tags

we have a database structured as follows:

-Cnsmr_accnt_tag Table (Important fields cnsmr_accnt_id, tag_id)
-Tag Table (Important fields Tag_id, tag_nm, Tag_typ_id)
-Tag_typ Table (Important fields Tag_typ_id, Tag_typ_nm)

The cnsmr_accnt can have multiple tags. Some types of tags are exclusive and other types are not.

So we have an exclusive tag type called 'Area'. (Tag_typ.tag_typ_nm = 'Area') and an account will only have tag with type 'Area'. Then within this we have another exclusive tag type called 'Process'. And finally we have a bunch of other tags which are non-exclusive.

So an account will be in one area and one process but could then have multiple non-exclusive tags.

I am trying to create a query to present data as follows:

CNSMR_ACCNT_ID       |    AREA_TAG     |     PROCESS TAG     |     OTHER TAGS
12345                |Prelegal         |   Early Stage       | System Status Tag
12345                |Prelegal         |   Early Stage       | Active Tag
12352                |Prelegal         |   Early Stage       | System Status Tag
12352                |Prelegal         |   Early Stage       | Active Tag
12370                |Legal            |   Early Stage       | System Status Tag
12370                |Legal            |   Early Stage       | Active Tag
12370                |Legal            |   Early Stage       | Other Tag1
12370                |Legal            |   Early Stage       | Other Tag2

Any suggestions as to how to do this?

What is the Max function equivalent of SQL in LINQ?

I have this SQL query that I need to convert to LINQ:

SELECT * FROM USER U
INNER JOIN (
    SELECT USERID , MAX(SALESDATE) AS MAXDATE
    FROM SALES
    GROUP BY USERID
) S ON U.ID = S.USERID

so far, this is what I've come up with LINQ:

var Users = (from d in db.Users
             join s in db.Sales on d.Id equals s.UserId
             select new Models.User
             {
                Id = d.Id,
                UserName = d.UserName,
                FirstName = d.FirstName,
                LastName = d.LastName,
                EmailAddress = d.EmailAddress,
                PhoneNumber = d.PhoneNumber,
                LastPurchase = s.SalesDate
             }).Max(x => x.SalesDate);

However, I'm pretty sure the result is not the same. Can anyone suggest the correct way of converting this to LINQ? I'm still new to learning LINQ. I really appreciate the help.

How to add company names to biggest file list in postgres

Postgres 9.1+ database contains different schema for every company named firma and company number, like firma1, firma5, firma99, firma12.

Every schema contains table with company name:

-- this table contains always exactly one row:
create table firma5.company ( company char(50) not null );

Following query list biggest tables:

select
(n.nspname||'.'||relname)::char(45) as tablename
    , pg_size_pretty(pg_total_relation_size(c.oid))::char(10) as totalsize
    , case
        when c.relkind='i' then 'index'
        when c.relkind='t' then 'toast'
        when c.relkind='r' then 'table'
        when c.relkind='v' then 'view'
        when c.relkind='c' then 'composite type'
        when c.relkind='S' then 'sequence'
        else c.relkind::text
      end ::char(14) as "type"
from
    pg_class c
    left join pg_namespace n on n.oid = c.relnamespace
    left join pg_tablespace t on t.oid = c.reltablespace
where
    (pg_total_relation_size(c.oid)>>20)>0 and c.relkind!='t'
order by
    pg_total_relation_size(c.oid) desc

This query shows company schemas like firma1, firma5 etc.

How to show company names ( firman.company.company fields ) also in this query result ? Query may return also tables from schemas other than firmaN . In this case company name column should be empty or null.

How can i get the path of .sql file in my php website

I have a website code and i don't know where to place the .sql file in my website folder.

How to get all rows that has same ID but diffrent value in other column?

I'm having a trouble writing a query in PostgreSQL. I have a View that contains IDs with subID. for example:

ID  quantity partID 
100   50       10
100   20       10
100   30       11
101   50       13
101   70       13
102   20       17

I want to get all rows that has same ID but different partIDs. for the given example I would like to get:

ID  quantity partID 
100   50       10
100   20       10
100   30       11

i tried this query query:

select id  ,quantity ,partid 
from A
group by id,quantity,partid
having count(id)>2
order by id

but it doesn't work. It accualty checks if ID appears in more than 2 rows... in the given example it will take ID 101 as well. I also don't know how to make it choose only partid which are diffrent per ID.

Entity Framework - code first - Too many navigation properties

I have two tables created with Entity Framework code first that I would like some help with..!

Tables

  1. AccountLinks, 3 composite keys
  2. Guest, 3 composite foreign keys (?)

Table overview

enter image description here

SQL overview

enter image description here

As you can see I have ALOT of navigation properties in my database which I dont want.

Code for AccountLink

public class AccountLink
    {
        public AccountLink()
        {
            AccountLinkPermissionAccountLinkID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionAccountOwnerID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionGuestID = new HashSet<AccountLinkPermission>();
        }

        public AccountLink(int accountOwnerID, int guestID, DateTime dateCreated, DateTime dateStart, DateTime dateExpires)
        {
            AccountLinkPermissionAccountLinkID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionAccountOwnerID = new HashSet<AccountLinkPermission>();
            AccountLinkPermissionGuestID = new HashSet<AccountLinkPermission>();
            this.AccountOwnerID = accountOwnerID;
            this.GuestID = guestID;
            this.DateCreated = dateCreated;
            this.DateStart = dateStart;
            this.DateExpires = dateExpires;
        }

        [Key, Column(Order = 0)]
        public int AccountLinkID { get; set; }
        [Key, Column(Order = 1)]
        public int AccountOwnerID { get; set; }
        [Key, Column(Order = 2)]
        public int GuestID { get; set; }

        public DateTime DateCreated { get; set; }
        public DateTime DateStart { get; set; }
        public DateTime DateExpires { get; set; }

        [ForeignKey("AccountOwnerID")]
        public virtual AccountOwner AccountOwner { get; set; }

        [ForeignKey("GuestID")]
        public virtual Guest Guest { get; set; }

        public virtual ICollection<AccountLinkPermission> AccountLinkPermissionAccountLinkID { get; set; }
        public virtual ICollection<AccountLinkPermission> AccountLinkPermissionAccountOwnerID { get; set; }
        public virtual ICollection<AccountLinkPermission> AccountLinkPermissionGuestID { get; set; }
    }

Code for AccountLinkPermissions

public class AccountLinkPermission
    {
        public AccountLinkPermission()
        {

        }

        public AccountLinkPermission(int accountLinkID, int accountOwnerID, int guestID, int permissionID)
        {
            this.AccountLinkID = accountLinkID;
            this.AccountOwnerID = accountOwnerID;
            this.GuestID = guestID;
            this.PermissionID = permissionID;
        }

        [Key, Column(Order = 0)]
        public int AccountLinkID { get; set; }
        [Key, Column(Order = 1)]
        public int AccountOwnerID { get; set; }
        [Key, Column(Order = 2)]
        public int GuestID { get; set; }
        [Key, Column(Order = 3)]
        public int PermissionID { get; set; }

        [InverseProperty("AccountLinkPermissionAccountLinkID")]
        public virtual AccountLink AccountLink { get; set; }

        [InverseProperty("AccountLinkPermissionAccountOwnerID")]
        public virtual AccountLink AccountLinkAccountOwner { get; set; }

        [InverseProperty("AccountLinkPermissionGuestID")]
        public virtual AccountLink AccountLinkGuest { get; set; }

        [ForeignKey("PermissionID")]
        public virtual Permission Permission { get; set; }
    }

The reason to why I want 3 composite keys is because I want to prevent duplicates.

Why I use InverseProperty instead of ForeignKey

Because I'm using multiple foreign keys linked to the same table, EF is not able to determine by convention which navigation properties belong together. Instead of using the property [ForeignKey] I have to use [InverseProperty] which defines the navigation property on the other end of the relationship.

What I need help with

How do I remove all the navigation properties in my database using code first? I know I messed it up somewhere but that's all I know :)

1 Navigation property in AccountLinks, (User_UserID)

9 Navigtion properties in AccountLinkPermissions

Bonus question

In AccountLink table I have three composite keys, AccountLinkID, AccountOwnerID and GuestID. Is it possible to put auto increment, (identity seed), on AccountLinkID? How would I do that in EF code first?

PL/Python: How to return an empty set?

I have a function that operates on int[]. I want to have the ability to check if the array is empty and in that case to stop since there is nothing to do.

this is my funcion:

CREATE OR REPLACE FUNCTION func2(c integer[])
  RETURNS SETOF func_type AS
$BODY$
result=plpy.execute("SELECT * FROM func1(ARRAY%s)"%c)
return result;
$BODY$
  LANGUAGE plpythonu VOLATILE

func_type defined as:

CREATE TYPE func_type AS
   (x integer,
    y numeric,
    z integer,
    zz integer);

when I added a check:

$BODY$
if not c:
    return  

I got an error:

ERROR:  returned object cannot be iterated
DETAIL:  PL/Python set-returning functions must return an iterable object.

I understand that I have to return func_type and func_type was not built since there was no plpy.execute but still how can I make it return at this point? Also, assume func3 called func2. How would func3 can check that there are 0 rows returned?

Literature list to master SQL / Oracle DB asap

I'm primary a web developer. During last 4 years I got experience with different programming languages and technologies. And now I made a decision to go on and to try something new. The result of my choice is diving into database administration.

Currently my knowledge in this sphere consist of:

  • base SQL knowledge (I know how to create/update/delete database structures, how to perform SELECT/INSERT queries with JOINS etc.),
  • experience in working with MySQL,
  • brief experience with Oracle DB

My biggest problem is that I'm relocating to another country after one month, so this is the time I have to prepare myself for the DBA vacancy. To avoid misunderstandings, I have to tell that I am a realist. And I perfectly understand that 30 days is an extremely short period of time and it's definitely not enough to become an expert in one of the IT spheres. What I am trying to achieve is to learn as much as possible during this period of time.

Could you please give me a peace of advice about what books may be useful for me in this situation? Currently I am finishing reading Learning SQL by Alan Beaulieu. I find it really useful to fresh up SQL language itself.

How to gather hierarchical data items and build a tree considering their dependencies

There is a table containing hierarchical data, e.g.:

| table "attribute_instances"                           |
+----+----------+------------+---------------+----------+
| id | tree_ref | parent_ref | attribute_ref | data_ref |
+----+----------+------------+---------------+----------+
|  1 |        1 |         -1 |             1 |        1 |
|  2 |        1 |          1 |             2 |        2 |
|  3 |        2 |         -1 |             1 |        3 |
|  4 |        2 |          3 |             2 |        2 |

It contains many separate trees (see tree_ref), each of them instantiating some attributes (see attribute_ref) and have a data reference data_reference, where data might be referenced in other trees, too.

Now, those trees should be merged into a single tree, in which (by now) up to 5 attributes may be chosen as level for that tree, e.g.:

attribute => level
------------------
        2 =>     1
        1 =>     2

What I need is one or more queries, that collects the data from table attribute_instances and gives a result as follows:

| table "merged_attribute_instances"         |
+----+------------+---------------+----------+
| id | parent_ref | attribute_ref | data_ref |
|  5 |         -1 |             2 |        2 |
|  6 |          5 |             1 |        1 |
|  7 |          5 |             1 |        3 |

This is the desired merged tree:

id:5 - data_ref:2
  id:6 - data_ref:1
  id:7 - data_ref:3

Note, that attribute_ref = 2 occurs only once in the resulting tree, as all instances of it have same data_ref value (that is 2).

I've tried some joins like

select *
  from attribute_instances a
  join attribute_instances b on a.tree_ref = b.tree_ref

But that seems to me being bad for having user-defined tree depth. I'm sure there is a better solution.

UPDATE: I should add, that table merged_attribute_instances is a temporary table. And the collecting query is iterated with for..do. In the loop the collected attribute_instances are then added to the temporary table.

SQL Server Update Query says table already exits

I have two tables in my database - MPRS and ALL_SSC. I want to update the ALL_SSC table with data from MPRS and in Access (where I test my SQL) it works.

However, when I run it in my VB 2010 program on the SQL Server database, it says table MPRS already exists.

I know it does! I'm not trying to create it. I'm updating FROM it... any ideas where this SQL is wrong?

SQL = "UPDATE ALL_SSC LEFT JOIN MPRS ON MPRS.MPAN = ALL_SSC.MPAN1 SET ALL_SSC.PC1 = Format([mprs].[pc],'00'), ALL_SSC.MSPCDC1 = Mid([mprs].[PC_EFD],7,4) & Mid([mprs].[PC_EFD],4,2) & Mid([mprs].[PC_EFD],1,2), ALL_SSC.MTSC1 = [mprs].[MTC], ALL_SSC.MSMCDC1 = Mid([mprs].[MTC_EFD],7,4) & Mid([mprs].[MTC_EFD],4,2) & Mid([mprs].[MTC_EFD],1,2), ALL_SSC.LLF1 = [mprs].[LLF], ALL_SSC.SUPPLIER1 = [mprs].[SUPPLIER], ALL_SSC.REGI1 = Mid([mprs].[SSD],7,4) & Mid([mprs].[SSD],4,2) & Mid([mprs].[SSD],1,2), ALL_SSC.ENG_STATUS1 = 0 WHERE (((ALL_SSC.MPAN1) Is Not Null) AND ([mprs].[ENERG_STATUS]='E'));"
cmd = New SqlCommand(sSQL, cNN)
Try
    Try
    If cNN.State <> ConnectionState.Open Then
        cNN.Open()
    End If

    Catch exCnn As Exception
    MsgBox(exCnn.Message)
    End Try
    cmd.ExecuteNonQuery()
Catch ex As Exception
    MsgBox("Cannot continue. " & ex.Message)
    Exit Sub
End Try

Troubles with using GROUP BY in SQL Query

I'm trying to write a propper SQL query in MS SQL Server. First of all, i have the following tables: Towns, Employees, Addresses. Almost every employee has Manager, whom ManagerID is foreign key in Employees also. (Self relation). My goal is to display the number of managers from each town. So far i have this code:

SELECT t.Name, COUNT(*) AS [Managers from each town] FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
GROUP BY t.Name
ORDER BY [Managers from each town] DESC

This query returns the number of Employees, from each town, not Managers. If i try the second query bellow, I get something totally wrong:

SELECT t.Name, COUNT(*) AS [Managers from each town] FROM Towns t
JOIN Addresses a
ON t.TownID = a.TownID
JOIN Employees e
ON a.AddressID = e.AddressID
JOIN Employees m
ON e.ManagerID = m.ManagerID

GROUP BY t.Name
ORDER BY [Managers from each town] DESC

Here is the structure of 'Employees' table:

EmployeeID, FirstName, LastName, MiddleName, JobTitle,DepartamentID, ManagerID, HireDate, Salary, AddressID

The correct query must return this result set:

Town          | Managers from each town
Issaquah      | 3
Kenmore       | 5
Monroe        | 2
Newport Hills | 1

how do i delete a row from the table that has the primary key used as foreign key in my other table?

i have created a foreign key relationship between two tables in my database diagram in sql server 2008. when i try to delete a row from the table containing the primary key there is an error. how do i delete a row from the table that has the primary key used as foreign key in my other table?

how to get deleted data from tables in Oracle

Can anyone help me retrieve deleted data in Oracle SQL*Plus?

here are the two tables that the data has been deleted from.be informed that only data of current date (T_DT = 28-JUN-15') has been accidentally deleted and the sales center id (SC_CD) selected as 27.

SQL> desc targ_mas
 Name                            Null?    Type
 ------------------------------- -------- ----
 SC_CD                           NOT NULL VARCHAR2(2)
 FP_ID                           NOT NULL VARCHAR2(4)
 T_DT                            NOT NULL DATE
 T_PCT                                    NUMBER(3)
 T_TARG                                   NUMBER(9,2)
 FL_MVH                          NOT NULL VARCHAR2(1)

SQL> 
SQL> 
SQL> desc prod_targ
 Name                            Null?    Type
 ------------------------------- -------- ----
 ASM_ID                                   VARCHAR2(4)
 RM_ID                                    VARCHAR2(4)
 FM_ID                                    VARCHAR2(4)
 FP_ID                           NOT NULL VARCHAR2(4)
 SC_CD                           NOT NULL VARCHAR2(2)
 TYP_CD                          NOT NULL VARCHAR2(2)
 DIV_CD                                   VARCHAR2(1)
 CAT_CD                          NOT NULL VARCHAR2(2)
 PRD_CD                          NOT NULL VARCHAR2(4)
 T_DT                            NOT NULL DATE
 QTY_TARG                                 NUMBER(8,2)
 T_VAL                                    NUMBER(10,2)
 TP_VAL                                   NUMBER(9,2)
 BR_CD                                    VARCHAR2(3)
 BS_CD                                    VARCHAR2(4)
 RMBS_CD                                  VARCHAR2(2)

queries used to delete data from both tables :

delete from targ_mas
where sc_cd = '27'
and t_dt = '28-JUN-15'


delete from prod_targ
where sc_cd = '27'
and t_dt = '28-JUN-15'

thanks. Iftekhar

samedi 27 juin 2015

Ordering dot-delimited numeric sequences (e.g., version numbers)

In my database I've column fill this data:

1
1.1
1.1.1
1.1.1.1
1.1.2
1.10
1.11
1.2
1.9

I want to sort it, to get result looks like this:

1
1.1
1.1.1
1.1.1.1
1.1.2
1.2
1.9    
1.10
1.11

How can I accomplish this? Simple use "ORDER BY" gives wrong result because it's lexicographic order.

NOLOGGING is not functioning

This is a part of my code. Everything is ok, no error. But I have million of lines to be inserted. I searched the internet for ways to finish my job faster.

What I found, is that insert in table using nologgin and +append, but is not working. Time to insert the same lines in table is the same even I use nologging and append.

    create or replace procedure read_files(input varchar2, extensie varchar2) as  
........................................ 
    Loop
     BEGIN
..............................
        UTL_FILE.GET_line(F1,V1);
    insert /*+ append */ into alarms(alarm_id,property_name,property_value) 
    =values(alarm_counter,f_property_name,f_property_value) ;

     End loop;
    end; 


alter table alarms nologging;
execute read_files('occ','cap');
alter table alarms logging;

Steps for my work:

  • first compile the procedure
  • alter table nologging
  • execute procedure

Where is my mistake?

SQL Server: IF EXISTS massively slowing down a query

(SQL Server 2012 being used)

I found some topics on query optimization, and comparing EXISTS to COUNT, but I couldn't find this exact problem.

I have a query that looks something like this:

select * from
tblAccount as acc
join tblUser as user on acc.AccountId = user.AccountId
join tblAddress as addr on acc.AccountId = addr.AccountId
... **a few more joins**
where acc.AccountId in (
    select * accountid from
    (select accountid, count(*) from tblUser
    where flag = 1
    group by accountId) as tbl where c != 1

This query runs in an instant (although the db is quite big, around 70Gb).

When I wrap the query in an EXISTS as in:

if exists
(
  **Exact same query as above**
)
begin
RAISERROR('Account found without exactly one flagged user.', 16, 1);
end
else
begin
  print 'test passed.'
end

Suddenly the query takes about 5-6 seconds to complete. I've tried specifying IF EXISTS (SELECT TOP 1 FROM... and also tried NOT EXISTS (which was even slower). But neither work to speed this up.

If the normal select query completes basically instantly, then does anyone know why wrapping it in the EXISTS causes so much extra computation? And/or anyone have any ideas to work around this (I'm just trying to throw an error if any records are found at all by the original query).

Thanks!

SQL Server | Is my stored procedure is OK?

CREATE PROCEDURE spCountTableRowWHere
    @TblName VARCHAR(50),
    @TblID VARCHAR(10) = 'Id',
    @WhereClause NVARCHAR(500) = '1=1'
AS
BEGIN
    DECLARE @Query NVARCHAR(500)
    DECLARE @ParamDefinition NVARCHAR(40)
    DECLARE @Count INT
    SET @Query = 'SELECT @C = COUNT('+@TblID+') FROM '+@TblName+' WHERE '+@WhereClause
    SET @ParamDefinition = '@C INT OUTPUT'

    EXECUTE SP_EXECUTESQL @Query, @ParamDefinition, @C = @Count OUTPUT
    SELECT @Count
END

I am new in SQL and I am wondering if this kind of procedure is better than a separate procedures for different tables.

How to store XML field values in database using php

I have an XML in local server. I want to store values in databse using Php. How to do it. Any sample code plz to achieve it.

SQL - Count of Sessions and transactions between session

I am trying to write a HiveQL (or even ANSI SQL) query that can answer the following:

I have a database with events (transactions), each event is stored with a user_id and a trans_time (transaction time). A user_id can have unlimited events, even possible to have multiple events with the same trans_time.

If I establish an activity window of time (i.e. 10 seconds), how many windows had how many events within that window? I'm thinking the result would be something like this (obviously without the words, just numbers)... 100 windows had only 1 event 50 windows had 2 events . . 1 window that had 30 events

To describe the first result...There were 100 instances in the data set where only 1 event took place within the 10 second window.

Is there a way to pull another metric out of the same data that says, 50 user_ids had only 1 event 25 user_ids had 2 events . . 1 user_id had 30 events

To describe the first result...There were 50 user_ids that had only 1 event within the 10 second window.

I hope this is not too nebulous. As always, thank you!!

Display the number of before held training

I am new in Laravel and I used Laravel 5 for my project. My specification is count the number of before held training according to category of training so i used join and group by and count function to get the number before held training:

In my controller code is as below:-

<?php

namespace App\Http\Controllers;
use Request;
use App\Training;
use App\Schedule;
use App\User;
use App\Http\Requests;
use App\Http\Controllers\Controller;
use App\Http\Requests\TrainingRequest;
use Carbon\Carbon;


  class TrainingController extends Controller
  {
/**
 * Display a listing of the resource.
 *
 * @return Response
 */

public function __construct(){

$this->middleware('auth',['only'=>'index']);
}
public function index()
{
    //


    $training=Training::all();

        $before_held=Schedule::join('training','training.id','=','schedule.training_id')
                 ->groupBy('training.category')->where('schedule.training_end_date','>=',carbon::now())->get();
   // echo count($before_held);
   //die();


    return view('Training.index',compact('training','before_held'));
}

In View:-

 <td><a href="/training/before_held">{{count($before_held)}}</a></td>

This always show 0 count in the table like this:-

|category|Held Before|
|test    |   0       |
|test    |   0       |

But I need count two if training_end_date greater than or equal to current time.

SO I am thankful if anyone can solve this issue.

Symfony2 twig print values from query on 2 non related tables

I have 2 entities: "Requests" and "Partners" There is a 1to1 relation between Requests and Partners so there is a partners_id field in Requests table I want to display a Partners list, but I need to take a value from a "price" field in Requests table

On my controller I have this query:

$listPartners = $em
    ->getRepository('OandPboBundle:Partners')
    ->createQueryBuilder('p')
    ->select('p')
    ->leftJoin('OandPboBundle:Requests', 'r', 'WITH', 'r.partners = p.id')        
    ->where('p.date LIKE :date AND p.active = :active')
           ->setParameter('date', '%'.$year.'-'.$month.'%')
           ->setParameter('active', 1)

    ->orderBy('p.date', 'DESC')
    ->addOrderBy('p.id', 'DESC')
    ->getQuery()
    ->getResult();

And in my TWIG file I have

{% for partners in listPartners %}
            <tr>
                <td>{{ partners.id }}</td>
                <td>{{ partners.price }}</td>
            </tr>
{% endfor %}

And of course there is an error because they say there is no "price" field in "Partners" (of course I want to take it from "Quotations")

Is there a way to do that?

mercredi 6 mai 2015

Find resolution of an image in dpi value uploaded using paperclip

In our Rails Application, we are uploading images using Paperclip gem. I would like to know whether there is anyway to find DPI value of such an uploaded image?

date based condition check in ruby

I want to check a category based on the age of a member. My test are looking like this:

it "should return 'CATEGORY B' if member turn 15 on the 01-11-2015" do
  Timecop.freeze(Date.parse("01-11-2015"))
  @member.date_of_birth = "2000-11-01"
  expect(@member.category).to eq('CATEGORY B')
end

it "should return 'CATEGORY C' if member turn 15 on the 31-10-2015" do
  Timecop.freeze(Date.parse("01-11-2015"))
  @member.date_of_birth = "2000-10-31"
  expect(@member.category).to eq('CATEGORY C')
end

and my method in the model is looking like this:

def category
  local_age = age
  next_november = "01-11-#{Date.today.year}"
  last_day_in_oktober = "31-10-#{Date.today.year}"
  if local_age < 7 then
    'CATEGORY A'
  elsif local_age >= 7 && local_age < 15
    'CATEGORY B'
  elsif local_age > 15 && local_age < 40
    'CATEGORY C'
  elsif local_age >= 40
    'CATEGORY D'
  end
end

How can I implement the and freeze the time in the model in order pass the tests? Any idea

Check if local variable is defined given it's name as string in ruby

Can I check if a local variable is defined given it's name as string?

I know there is the function defined?, but you have to give the variable itself.

Example:

a = 'cat'
print defined?(a) # => "cat"
print defined?(b) # => nil

What I need is:

a = 'cat'
print string_defined?("a") # => "cat"
print string_defined?("b") # => nil

Or something like that. I can't find it in the docs...

I tried to use respond_to?, but doesn't seems to work...

Ruby Sinatra - How to capture post json data and save to file

How do I capture a Json data from POST route and save it to file? I have simple ruby sinatra code as below.

#!/usr/bin/env ruby
require 'rubygems'
require 'sinatra'
require 'json'

post '/' do
  values = JSON.parse(request.env["rack.input"].read)
# How do I save "values" of JSON to file..
end

How to add front end validation in bootstrap-wysihtml5-rails

I have integrated the bootstrap-wysihtml5 editor to description section in my rails application. Now I want to add the client side validation so that it would validate the presence of description field. I used bootstrap-wysihtml5-rails gem.

Thanks in advance.

How can I check whether new page is completed loaded or not using watir

Is there any way Can I check whether a particular page completely loaded or not using WATIR?

I tried with browser.status but it's not printing anything

Ruby ternary operator and method call

I am using ruby 2.1.5, facing some problem with ternary operator

Syntax error

request.xhr?  ? render :json => "success"  : redirect_to index_url

working

request.xhr?  ? render(:json => "success") : redirect_to(index_url)

Can some please explain How its works and why above one not working? Thanks in advance

Ruby on Rails: Categories not working or sorting

The posts and categories are not agreeing on something and I'm not sure what it is. What am I doing wrong?

As soon as I click a category nothing changes but the url to http://localhost:3000/posts?category=Physics

class Post < ActiveRecord::Base
  belongs_to :category
  # ...
end

class Category < ActiveRecord::Base
  belongs_to :posts
  # ...
end

Here is the Posts Controller:

class PostsController < ApplicationController     

    def index
        if 
            params[:category_id].blank?
            @posts = Post.all.order("created_at DESC")
        else
            @category_id = Category.find_by(name: params[:category]).id
            @posts = Post.where(category_id: @category_id).order("created_at DESC")
        end
    end

# ...
    def post_params
        params.require(:post).permit(:title, :link, :description, :category_id)
    end

Here is the index file:

<% Category.all.each do |category| %>
    <%= link_to category.name, posts_path(category: category.name) %>
    <% end %>


          <% @posts.each do |post| %>
              <%= link_to post.title, post  %>
            <%= link_to time_ago_in_words(post.created_at) + " ago", post %>  
          <% end %>

This is the migration for Posts:

  class CreatePosts < ActiveRecord::Migration
  def change
    create_table :posts do |t|
      t.string :title
      t.string :link
      t.text :description

      t.timestamps null: false
    end
  end
end

This is the migration for AddIdToPosts

class AddIdToPosts < ActiveRecord::Migration
  def change
      add_column :posts, :category_id, :integer
  end
end

This is what console told me about the last post:

irb(main):008:0> Category

=> Category(id: integer, name: string, created_at: datetime, updated_at: datetime)

irb(main):009:0> @post = Post.last

  Post Load (17.0ms)  SELECT  "posts".* FROM "posts"  ORDER BY "posts"."id" DESC LIMIT 1

=> #<Post id: 3, title: "Testing", link: "", 
       description: "testing", created_at: "2015-05-06 07:08:36", 
       updated_at: "2015-05-06 07:08:36", user_id: 1, category_id: 1>

Ruby on Rails uncaught throw :warden with Devise.

I have a very strange problem in my Ruby on Rails application. I have controller that looks like this:

class PersonReportsController < ApplicationController
  include ActionController::Live
  load_and_authorize_resource except: [:index]

  def index
    #some code
  end
end

and when I try to access it as an unlogged user application throws following error:

uncaught throw :warden

But when I delete from my controller:

include ActionController::Live

everything works fine... Any ideas how to solve this problem?

javascript code disappears on source window after refreshing page in rails 3 using jquery

Here is a unique problem I am not able to find solution to. I write a small black of code given as below.

$(document).ready(function () {
alert("Page loaded");
if (/iPhone|iPad|iPod/i.test(navigator.userAgent)) {
    $(document).on('visibilitychange', function () {
        if (document.hidden == false) {
            console.log("gotcha");
            location.reload();
        }
        console.log(document.hidden, document.visibilityState);
    });
}
});

I was working only for ipad(the bug was device specific) but now I face the issue that this code disappears after refreshing page a few times in all other machines also.

Solutions attempted till now:

  1. I added this within the tag with and without specifying type="text/javascript" in the beginning of the page and in the end as well. Astonishingly this code disappears the same way and few other variables defined for javascript code within the tag remains same and visible.
  2. I added a file separately with this block of code in it and included that file with <%= javascript_include_tag %> in two ways.

    2.1 Simply adding this tag in my separate view file.

    2.2 Adding it using "content_for" helper and thus calling it with yield and specified name in the application.html.erb file.

3.When I add this in my appliaction.js within assets folder it works but I need this specifically for a particular action else it will reduce my site's performance .

I do not understand whether the issue is with the code or what. I will be really thankful if someone provides a required solution to it.

I am using following things in it

ruby 1.9.3

rails 3.2.12

running server with foreman

memcache

Foreman conditional running processes

I have a foreman app with a Procfile like this

web: bundle exec rails s
custom_process: bundle exec rake custom:process
faking_custom_process: bundle exec rake custom:faking_process

And I want to run custom_process or faking custom_process depends on my needs:

foreman start # run web & custom_process
FAKING_PROCESS # run web & faking_custom_process

Yes, I know about ability of running like that foreman start -c faking_custom_process=0, but this is more difficult than I expect, right?

How can I connect Aweber to my Rails app using OAuth?

I'm trying to integrate my Rails app with Aweber via OAuth, using the official aweber gem.

If I follow their flow in the Rails console, I can get an access token, no problems:

oauth = AWeber::OAuth.new(ENV["AWEBER_CONSUMER_KEY"], ENV["AWEBER_CONSUMER_SECRET"])
puts oauth.request_token.authorize_url
# => http://ift.tt/1JpWwfi

Then I visit that URL, type in my credentials, get a verification code, and go back to the rails console:

oauth.authorize_with_verifier 'xxxxxx'
# => #<OAuth::AccessToken>

Success!

The problem is, I want to do this in the real world, not just at the console, which means my Ruby code needs to be broken up into two separate actions. First, there's the controller action which redirects to Aweber's Oauth page:

def aweber
  oauth = AWeber::OAuth.new(ENV["AWEBER_CONSUMER_KEY"], ENV["AWEBER_CONSUMER_SECRET"])
  redirect_to  oauth.request_token(oauth_callback: "http://ift.tt/1H0hUKK").authorize_url
end

Then there's the action which gets the access token after the user has input their credentials and been redirected:

def aweber_callback
  oauth = AWeber::OAuth.new(ENV["AWEBER_CONSUMER_KEY"], ENV["AWEBER_CONSUMER_SECRET"])
  oauth.authorize_with_verifier(params[:oauth_verifier]) 
end

When I do it this way, the final line (authorize_with_verifier) always raises #<OAuth::Unauthorized: 401 Unauthorized>.

Seems like the problem is that I'm initializing the oauth variable twice, meaning I have two unrelated instances of AWeber::Oauth ... and only the instance of AWeber::Oauth that generated the authorize_url can get the access token. But I can't get the *same* instance in bothaweber_callbackandaweber` because I'm dealing with two completely different threads and instances of the controller.

When I inspect oauth, I can see that the internal variables oauth.request_token.params["oauth_token"] and oauth.request_token.params["oauth_token_secret"] are different in each oauth, which I'm guessing is the cause of the problem. I can get the 'correct' oauth_token from the params (params[:oauth_token]), but I can't figure out how to get the correct oauth_token_secret (not to mention that manually setting instance variables like this feels very hacky and is probably not the best approach.)

How can I generate an access token?

Ruby on rails plugin

I´m new in Ruby on rails. I´m trying to write a plugin, place in in project menu, and insert some data per projects. but there is a problem in my plugin. I have written the plugin, and add a tab in project menu. the plugin show something, but now I wanna edit the data. in the page index.html.erb, show data. I have added a link to edit data. I add a page edit.html.erb. but when I click on the edit link, show that the page can not found. Can anyone help me_

Get sum of numbers within range (from 0 to user input)

This is the code i am using, its purpose is for the user to enter an integer, the program will then take the sum of all the numbers up to and including the one entered. There is probable an easier way to do this

 sum = 0
 puts "please enter a number"
 counter = gets.chomp.to_i
 begin
  sum += counter
  counter -= 1
 end while counter == 0

Ruby on Rails on Windows 'SQLite' installing error

error on windows installing sqlites I am facing this problem any one can help me. Ruby version 2.0.0 and rails version 4.2.1.

I have an array called p1 as below:

p1= [512, 2048]

I'm passing p1 to test method as below:

test(p1)

test method definition looks as below:

def test(value)
  if value.kind_of?(Array)
    value.each do |proto|
      puts"******#{proto}"
    end
  end
end

The above code execution is throwing the error:

#<NoMethodError: undefined methodto_i' for [512, 2048]:Array>

What could be the reason for it?

Get Postgres-activerecord setup to work both locally and in Heroku

I'm automating DB creation (with a Rakefile in a Sinatra App).

I would like to be able to run the rakefile from my Linux user "pete" (eg pete@pete_laptop: /path $ rake db:create) AND from Heroku.

It comes down to the settings in my config/database.rb:

ActiveRecord::Base.establish_connection(
  :adapter  => db.scheme == 'postgres' ? 'postgresql' : db.scheme,
  :host     => db.host,
  :port     => db.port,

  # pete@ubuntu_14.04_laptop--------
  # :username => 'pete',
  # :password => 'password',
  # OR
  # heroku -----------------
  # :username => db.user,
  # :password => db.password,

  :database => DB_NAME,
  :encoding => 'utf8'
)

If I use the pete@ubuntu_laptop settings, the database works in localhost but not in Heroku, If I use the heroku settings, the database works in localhost but not in Heroku.

How can I setup this file/my ubuntu laptop so that the app works both on localhost & in Heroku?

Cheers,

Pete

Scope on empty association

I'm trying to create a scope looking at empty association.

I've 4 classes : User, Idea Project and UserJoins.

More than one user can have the same idea or the same project.

I would like to create a scope to isolate users without ideas.

Idea.rb

has_many :user_joins
has_many :users, through: :user_joins

Project.rb

has_many :user_joins
has_many :users, through: :user_joins

User.rb

has_many :user_joins
has_many :ideas, through: user_joins, source: :imaginable, source_type: 'Idea'
has_many :projects, through: user_joins, source: :imaginable, source_type: 'Project'

scope :without_ideas, ->{
  # I'm stuck here.
}

UserJoin.rb

belongs_to :imaginable, polymorphic: true
belongs_to :user

I'm using Rails 3.2.17 and Ruby 2.0.0

Does anyone have an idea to solve this ?

mardi 5 mai 2015

Looking to Pinged a Series of Servers on a Subsecond Basis and Got a Response for Each

I'm trying to figure out the solution to what seems like a simple problem. I have a series of servers that I'd like to ping and get a response from which I will parse and present to the user. I've looked at a bunch of ruby frameworks and Gems, and none-seem like a perfect fit. Currently, I'm using:

  • Sinatra
  • Passenger
  • Nginx
  • Docker

The servers will likely be behind a firewall, and only visible to the node I'm running in the same group, so Javascript on the browser does not feel like a fit. Is there a known pattern for doing something like this?

How to use with_options for conditional validation

How can I use with_options for conditional validation ?

My code is

with_options if: (AppUser::User.creator=="is_admin") do |admin|
  admin.validates :first_name, :presence => true
  admin.validates :last_name, :presence => true
end

I have already set creator method in application controller.

before_action :set_global_user

def set_global_user
  if current_admin
    AppUser::User.creator= "is_admin"
  elsif current_user
    AppUser::User.creator= "is_user"
  else
    AppUser::User.creator=nil
  end
end

but I am getting

undefined method `validate' for false:FalseClass

what is wrong with this code.

Rails Undefined Method in an unknown way

I cloned a project that runs just fine. I did the bundle install, bundle update, db:create, db:migrate, and whatnot.

Now when I run http://localhost:3000/orders/new it returns a NoMethodError in Admin::Orders#new

I have this as part of my code under model/admin/area.rb

has_many :admin_accounts, :class_name => 'Admin::Account'

validates :name, :presence => true
validates :timezone, :presence => true
validates :time_offset, :presence => true
validates :shift_time1, :presence => true
validates :shift_time2, :presence => true
validates :unit_price_12, :presence => true
validates :unit_price_24, :presence => true

def next_shift_time_from_now
  current_time = Time.zone.now
  next_shift_time(current_time)
end

def next_shift_time(time)
  shift1 = time.change(hour: self[:shift_time1].hour)
  shift2 = time.change(hour: self[:shift_time2].hour)
  get_shift(time, shift1, shift2)
end

And this under my accounts.rb

require 'digest/md5'
before_save :encrypt_password
after_initialize :default_values

has_many :admin_orders, :class_name => 'Admin::Order'
belongs_to :admin_areas, :class_name => 'Admin::Area', :foreign_key => 'area_id'

under new.html

<div class="page-header clearfix">
  <div class="pull-right"><%= link_to 'Back', orders_path, class: "btn btn-primary btn-sm" %></div>
  <h3 class="pull-left">
    Creating New Order
  </h3>
</div>

<%= render 'form' %>

and this under _form.html.erb which has the error

    <div class="form-group field-bottom-container">
      <label id="nextShiftTime">Next Shift Time</label>

      <div>
        <% @user = Admin::Account.find($user_id) %>
        <%= @nextShiftTime=format_datetime(@user.admin_areas.next_shift_time_from_now) %>
      </div>
    </div>

The error is this line: <%= @nextShiftTime=format_datetime(@user.admin_areas.next_shift_time_from_now) %>

which returns... undefined method `next_shift_time_from_now' for nil:NilClass

Is there anything I must configure to fix this? I think there's nothing wrong with the code since it worked well previously and upon recent clone, with no changes with the codes i might add, the program is causing this error.

How to install ruby on rail application as a plugin in my application?

I am already using open source RoR application. Now I have to create plugin. In that plugin the features I want to use are available in other application. So i want to use that. How can it be done ?

What should be stored in DB when converting site into rails [on hold]

I believe this is more of a subjective question than not, that being said, I am much newer to rails than a lot of you, so I trust your opinion more than my own. Basically I've been talking to someone I know about redoing there website into a rails app, but I am not sure how the best way to go about setting up the database would be. Here is a link to the site I will be remaking http://ift.tt/1OY6mvK.

My question to you is, what all belongs in a database? My first thought is to create two main tables in the db, sections and articles, and have each article belong to a section. I will store all of the html for the content of each separate pages in the db under a certain section (for instance the "welcome", "FAQs", "Contact Information", and all other links under the about dropdown menu will be separate articles stored in the db which belong to the "About" section.)

**Update 5/05/2015 2:37 PM CDT I should clarify that when I say I will post the html for each of those pages in the database, I do not mean ALL of the html on that given page, just the content on those pages that is unique to that page (so headers, menu bars, etc. will not be stored in the database)

However, I'm beginning to wonder if storing all that html and text in a database would be too much, or would be bad practice, and if it is better to just create separate html files for each page, too make it easier to style text and create tables within text and stuff like that. I see pros and cons to both sides, a huge pro, for example, is the fact that if I did store it in the database I would not need to create 30+ HTML files, and could just pass in different Articles from the database into a preformatted page.

So I'm just looking for some advice on what you guys think is the best way to go about this project!

Remove array elements that are present in another array

There is a list of words and list of banned words. I want to go through the word list and redact all the banned words. This is what I ended up doing (notice the catched boolean):

puts "Give input text:"
text = gets.chomp
puts "Give redacted word:"
redacted = gets.chomp

words = text.split(" ")
redacted = redacted.split(" ")
catched = false

words.each do |word|
  redacted.each do |redacted_word|
    if word == redacted_word
        catched = true
        print "REDACTED "
        break
    end
  end
    if catched == true
        catched = false
    else
        print word + " "
    end
end

Is there any proper/efficient way?

Getting error while executing and calabash program

Hi I am getting the below error

D:>calabash-android run d:\Android\Apk_Files\EriBank.apk C:/Ruby21/lib/ruby/2.1.0/rubygems/dependency.rb:298:in to_specs': Could not fi d 'cucumber' (~> 1.3.17) - did find: [cucumber-2.0.0] (Gem::LoadError) from C:/Ruby21/lib/ruby/2.1.0/rubygems/specification.rb:1295:inblock n activate_dependencies' from C:/Ruby21/lib/ruby/2.1.0/rubygems/specification.rb:1284:in each' from C:/Ruby21/lib/ruby/2.1.0/rubygems/specification.rb:1284:inactiva e_dependencies' from C:/Ruby21/lib/ruby/2.1.0/rubygems/specification.rb:1266:in activa e' from C:/Ruby21/lib/ruby/2.1.0/rubygems/core_ext/kernel_gem.rb:54:inge ' from C:/Ruby21/bin/calabash-android:22:in `'

Please help me out if anyone knows any solutions for this.

In ruby console, how to update instance variable?

I have a model named "Person". In ruby console, I first declare an instance of Person, then I update the attributes, then save.

person = Person.last
person.name = "jeff"
person.save

After doing these, I got message like this:

   (9.9ms)  BEGIN
   (7.5ms)  ROLLBACK
=> false

What are "BEGIN", "ROLLBACK", "false" refers to separately? I googled, but nothing came out.

Push value to model when created - Rails

I am trying to set the inputter_id value automatically to the id for for the inputter_type corresponding to "Medical team" in a form if the user is a current_clinician:

  def create
    esas_assessment_params = params.require(:esas_assessment).permit(:patient_id, :clinician_id, :time, :year, :month, :day, :inputter_name, :inputter_id, :pain, :pain_comment, :tiredness, :tiredness_comment, :drowsiness, :drowsiness_comment, :nausea, :nausea_comment, :lack_of_appetite, :lack_of_appetite_comment, :shortness_of_breath, :shortness_of_breath_comment, :depression, :depression_comment, :wellbeing, :wellbeing_comment, :other_symptom_id, :other_symptom_score, :other_symptom_comment, :esas_comment)
    @esas_assessment = EsasAssessment.new(esas_assessment_params)
    if current_clinician
      @esas_assessment.clinician = current_user.clinician
      @esas_assessment.inputter_name = current_user.clinician.full_name
      @esas_assessment.inputter_id = Inputter.find_by(inputter_type: 'Medical team')
    else
      @esas_assessment.patient = current_user.patient
      @esas_assessment.clinician = current_user.patient.clinician
    end
    if @esas_assessment.save
      redirect_to esas_assessments_path, notice: "ESAS assessment submitted!"
    else
      render "new", alert: "ESAS assessment not submitted!"
    end
  end

or more simply:

  def create
    esas_assessment_params = params.require(:esas_assessment).permit!
    @esas_assessment = EsasAssessment.new(esas_assessment_params)

     @esas_assessment.inputter_id = Inputter.find_by(inputter_type: 'Medical team')

    if @esas_assessment.save
      redirect_to esas_assessments_path, notice: "ESAS assessment submitted!"
    else
      render "new", alert: "ESAS assessment not submitted!"
    end
  end

in the first example the lines to set the @esas_assessment.clinician value automatically to the current_user.clinician and setting the inputter_name both work but the inputter_id doesn't.

My EsasAssessment model is:

EsasAssessment:
  patient_id: integer
  clinician_id: integer
  created_at: datetime
  updated_at: datetime
  inputter_name: string
  inputter_id: integer

class EsasAssessment < ActiveRecord::Base
    belongs_to :other_symptom
    belongs_to :clinician
    belongs_to :patient
    belongs_to :inputter
end

Inputter is:

Inputter:
  inputter_type: string

class Inputter < ActiveRecord::Base
    has_many :esas_assessments
end

When I submit my form I don't get any errors or warning, inputter_id is just nil

if I enter

Inputter.find_by(inputter_type: 'Medical team')

in Console it returns

#<Inputter {"id"=>309, "inputter_type"=>"Medical team"}>

Any advice on how to get the value to stick to the new EsasAssessment would be great

Ruby rails can't add user entry

Everything seems to go fine and there's no error message when executing user.save, but it doesn't show up when indexing.

I ran the migration:

class AddEmailToUsers < ActiveRecord::Migration
  def change
    add_column :email, :name, :password, :password_confirmation, :string
  end
end

models/user.rb

class User < ActiveRecord::Base
  #attr_accessor :remember_token
  attr_accessor :email
  attr_accessor :name
  attr_accessor :password_digest
  attr_accessor :password, :password_confirmation
  before_save { self.email = email.downcase }
  validates :name,  presence: true, length: { maximum: 50 }
  VALID_EMAIL_REGEX = /\A[\w+\-.]+@[a-z\d\-.]+\.[a-z]+\z/i
  validates :email, presence: true, length: { maximum: 255 },
                    format: { with: VALID_EMAIL_REGEX },
                    uniqueness: { case_sensitive: false }
 # has_secure_password
  validates :password, length: { minimum: 6 }

  # functions...
end

Here on create I fill in some sample values to test it, and it submits to views/users/create.html.erb without anything displaying in "Error: []" and the correct data shown for the fields of @user.

controllers/user_controller.rb

class UsersController < ApplicationController
  def new
    #require 'bcrypt'
    @user = User.new
  end

  def index
    @users = User.all
  end

  def show
    @user = User.find(params[:id])
  end

 def create
    #@user = User.new(user_params)
    @user = User.new(name: "di3", email:"di3@di3.com", password: "di3", password_confirmation: "di3")
    #@user = User.new(params[:user])
    if @user.save!
      log_in @user
      flash[:success] = "Welcome to the forum!"
      #redirect_to @user
      #redirect_to users_path
    else
      #render 'new'
      render @user.error_messages.full
    end
  end

  private

  def user_params
      params.require(:user).permit(:name, :email, :password, :password_confirmation)
    end
end

views/users/create.html.erb

<h1>Create user params</h1>

<%= render partial: 'layouts/header' %>

<table>
  <tr>
    <th>Email</th>
    <th>Name</th>
    <th>Password</th>
  </tr>

      <tr>
        <td><%= @user.email %></td>
        <td><%= @user.name %></td>
        <td><%= @user.password %></td>
        <td><%= @user.password_confirmation %></td>
      </tr>
</table>

<br/>

Error:

<%= @user.errors.full_messages %>

But this user index doesn't show any users at all:

views/users/index.html.erb

<h1>Listing forums</h1>

<%= render partial: 'layouts/header' %>

<table>
  <tr>
    <th>Email</th>
    <th>Name</th>
    <th>Password</th>
    <th>Password</th>
    <th>Password</th>
  </tr>

  <% if(@users)
    @users.each do |user| %>
      <tr>
        <td><%= user.email %></td>
        <td><%= user.name %></td>
        <td><%= user.password_digest %></td>
        <td><%= user.password %></td>
        <td><%= user.password_confirmation %></td>
      </tr>
    <% end %>
  <% end %>
</table>

Rails: Tips for parsing JSON [on hold]

I'm relatively new to rails. Was curious if I'm parsing JSON the 'right/best' way. So I'm trying to get a list of subreddits from Reddit so I used the subreddits JSON from Reddit.

Here is what I first did to convert the JSON to hash -

require 'open-uri'

hash = JSON.load(open('http://ift.tt/1k5Yusm'));

I then, got the list display_name (subreddit names) from my hash the following way -

subreddit_arr = hash["data"]["children"].map { |c| c["data"]["display_name"]}

which gave me -

["funny", "pics", "AdviceAnimals", "aww", "todayilearned", "videos", "gaming", "gifs", "BlackPeopleTwitter", "WTF", "fatpeoplehate", "leagueoflegends", "AskReddit", "pcmasterrace", "me_irl", "worldnews", "news", "Showerthoughts", "DotA2", "4chan", "mildlyinteresting", "TrollXChromosomes", "nba", "politics", "trees"]

How can I get a list of all subreddits (not just 25) in the most efficient manner?

Ruby / Brew Stopped Working Yosemite

I'm fairly new to this so hang with me..

I have been searching for a solution to this problem for a week now.

Ruby does not seem to be working

ruby
-bash: ruby: command not found

or

ruby -v
-bash: ruby: command not found

and whenever I try homebrew this is what comes back

/usr/local/bin/brew: /usr/local/Library/brew.rb:     /System/Library/Frameworks/Ruby.framework/Versions/Current/usr/bin/ruby: bad     interpreter: No such file or directory
/usr/local/bin/brew: line 21: /usr/local/Library/brew.rb: Undefined error: 0

as you can see above I had changed the ruby framework to read from 1.8 to Current as others had suggested, however this did not solve the problem as it did for users who had encountered the same issue.

So the search continues on...

My usr/local/bin/brew script looks like this -

#!/System/Library/Frameworks/Ruby.framework/Versions/Current/usr/bin/ruby -W0
# encoding: UTF-8

std_trap = trap("INT") { exit! 130 } # no backtrace thanks

HOMEBREW_BREW_FILE = ENV['HOMEBREW_BREW_FILE']

if ARGV == %w{--prefix}
  puts File.dirname(File.dirname(HOMEBREW_BREW_FILE))
  exit 0
end

require 'pathname'
HOMEBREW_LIBRARY_PATH =     Pathname.new(__FILE__).realpath.dirname.parent.join("Library/Homebrew").to_s
$:.unshift(HOMEBREW_LIBRARY_PATH + '/vendor')
$:.unshift(HOMEBREW_LIBRARY_PATH)
require 'global'

case ARGV.first when '-h', '--help', '--usage', '-?', 'help', nil
  require 'cmd/help'
  puts Homebrew.help_s
  exit ARGV.first ? 0 : 1
when '--version'
  puts HOMEBREW_VERSION
  exit 0
when '-v'
  puts "Homebrew #{HOMEBREW_VERSION}"
  # Shift the -v to the end of the parameter list
  ARGV << ARGV.shift
  # If no other arguments, just quit here.
  exit 0 if ARGV.length == 1
end

   # Check for bad xcode-select before anything else, because `doctor` and
# many other things will hang
# Note that this bug was fixed in 10.9
if OS.mac? && `xcode-select -print-path 2>/dev/null`.chomp == '/' &&     MacOS.version < :mavericks
  ofail <<-EOS.undent
  Your xcode-select path is currently set to '/'.
  This causes the `xcrun` tool to hang, and can render Homebrew unusable.
  If you are using Xcode, you should:
    sudo xcode-select -switch /Applications/Xcode.app
  Otherwise, you should:
    sudo rm -rf /usr/share/xcode-select
 EOS

  exit 1
end

case HOMEBREW_PREFIX.to_s when '/', '/usr'
  # it may work, but I only see pain this route and don't want to support it
  abort "Cowardly refusing to continue at this prefix: #{HOMEBREW_PREFIX}"
end
if OS.mac? and MacOS.version < "10.5"
  abort <<-EOABORT.undent
    Homebrew requires Leopard or higher. For Tiger support, see:
    http://ift.tt/17h053J
  EOABORT
end

# Many Pathname operations use getwd when they shouldn't, and then throw
# odd exceptions. Reduce our support burden by showing a user-friendly error.
Dir.getwd rescue abort "The current working directory doesn't exist, cannot proceed."


def require? path
  require path.to_s.chomp
rescue LoadError => e
  # HACK :( because we should raise on syntax errors but
  # not if the file doesn't exist. TODO make robust!
  raise unless e.to_s.include? path
end

begin
  trap("INT", std_trap) # restore default CTRL-C handler

  aliases = {'ls' => 'list',
             'homepage' => 'home',
             '-S' => 'search',
             'up' => 'update',
             'ln' => 'link',
             'instal' => 'install', # gem does the same
             'rm' => 'uninstall',
             'remove' => 'uninstall',
             'configure' => 'diy',
             'abv' => 'info',
             'dr' => 'doctor',
             '--repo' => '--repository',
             'environment' => '--env',
             '-c1' => '--config',
             }

  cmd = ARGV.shift
  cmd = aliases[cmd] if aliases[cmd]

  sudo_check = Set.new %w[ install link pin unpin upgrade ]

  if sudo_check.include? cmd
    if Process.uid.zero? and not File.stat(HOMEBREW_BREW_FILE).uid.zero?
      raise "Cowardly refusing to `sudo brew #{cmd}`\n#{SUDO_BAD_ERRMSG}"
    end
  end

  # Add contributed commands to PATH before checking.
  ENV['PATH'] += ":#{HOMEBREW_CONTRIB}/cmd"
  if require? HOMEBREW_REPOSITORY/"Library/Homebrew/cmd"/cmd
    Homebrew.send cmd.to_s.gsub('-', '_').downcase
  elsif which "brew-#{cmd}"
    %w[CACHE CELLAR LIBRARY_PATH PREFIX REPOSITORY].each do |e|
      ENV["HOMEBREW_#{e}"] = Object.const_get "HOMEBREW_#{e}"
    end
    exec "brew-#{cmd}", *ARGV
  elsif require? which("brew-#{cmd}.rb").to_s
    exit 0
  else
    onoe "Unknown command: #{cmd}"
    exit 1
  end

rescue FormulaUnspecifiedError
  abort "This command requires a formula argument"
rescue KegUnspecifiedError
  abort "This command requires a keg argument"
rescue UsageError
  onoe "Invalid usage"
  abort ARGV.usage
rescue SystemExit
  puts "Kernel.exit" if ARGV.verbose?
  raise
rescue Interrupt => e
  puts # seemingly a newline is typical
  exit 130
rescue BuildError => e
  e.dump
  exit 1
rescue RuntimeError, SystemCallError => e
  raise if e.message.empty?
  onoe e
  puts e.backtrace if ARGV.debug?
  exit 1
rescue Exception => e
  onoe e
  puts "#{Tty.white}Please report this bug:"
  puts "    #{Tty.em}#{ISSUES_URL}#{Tty.reset}"
  puts e.backtrace
  exit 1
else
  exit 1 if Homebrew.failed?
end

    enter code here

Does any of this make sense or ring any bells? Could it have anything to do with why I'm getting the Homebrew or Ruby error?

Any help would be greatly appreciated.

I'd be happy to provide as much information as possible for us to solve this problem! :)

ruby idiom for update or insert a hashmap

Is there a common ruby idiom for this code:

if hashmap.has_key?(key)
    hashmap[key] += 1
else
    hashmap[key] = 1
end

It feels like there might be a high order function that helps here. I'm hoping for something like

hashmap[key].insertOrUpdate { 1 }, {|value| value += 1}

How to combine two same blocks into a single one?

How can I use a single block in this:

devise_parameter_sanitizer.for(:sign_up) do |u|
  u.permit(:full_name, :email, :password, :password_confirmation)
end
devise_parameter_sanitizer.for(:account_update) do |u|
  u.permit(:full_name, :email, :password, :password_confirmation)
end

Counting the Days from the current_level?

There are 5 levels.

Each level has a certain amount of days in it that must pass before the habit can move up to the next level (as broken down by n_days):

      case n_days     
          when 0..9
            1
          when 10..24
            2
          when 25..44
            3  #Level 3
          when 45..69
            4
          when 70..99
            5
          else
            "Mastery"
         end
      end

How can we call the n_days from the present level in the habits index with something like <%= habit.current_level.n_days.count_off_from_zero_to_show %>?

For example, if we are specifically at 50 on level 3 it would show Day 5 in the habits index.

habit.rb

class Habit < ActiveRecord::Base
    belongs_to :user
    has_many :comments, as: :commentable
    has_many :levels
    serialize :committed, Array
    validates :date_started, presence: true
    before_save :current_level
    acts_as_taggable
    scope :private_submit, -> { where(private_submit: true) }
    scope :public_submit, -> { where(private_submit: false) }

attr_accessor :missed_one, :missed_two, :missed_three

    def save_with_current_level
        self.levels.build
        self.levels.build
        self.levels.build
        self.levels.build
        self.levels.build
        self.save
    end

    def self.committed_for_today
    today_name = Date::DAYNAMES[Date.today.wday].downcase
    ids = all.select { |h| h.committed.include? today_name }.map(&:id)
    where(id: ids)
  end 

    def current_level_strike
      levels[current_level - 1] # remember arrays indexes start at 0
    end

    def current_level
            return 0 unless date_started
            committed_wdays = committed.map { |day| Date::DAYNAMES.index(day.titleize) }
            n_days = ((date_started.to_date)..Date.today).count { |date| committed_wdays.include? date.wday } - self.missed_days

      case n_days     
          when 0..9
            1
          when 10..24
            2
          when 25..44
            3
          when 45..69
            4
          when 70..99
            5
          else
            "Mastery"
        end
    end
end

level.rb

class Level < ActiveRecord::Base
  belongs_to :habit
end

schema

create_table "habits", force: true do |t|
  t.integer  "missed_days",    default: 0
  t.text     "committed"
  t.integer  "days_lost",   default: 0
  t.datetime "date_started"
  t.string   "trigger"
  t.string   "target"
  t.string   "reward"
  t.boolean  "private_submit"
  t.integer  "user_id"
  t.datetime "created_at",                 null: false
  t.datetime "updated_at",                 null: false
  t.integer  "order"
end

add_index "habits", ["user_id", "created_at"], name: "index_habits_on_user_id_and_created_at"
add_index "habits", ["user_id"], name: "index_habits_on_user_id"

create_table "levels", force: true do |t|
  t.integer  "habit_id"
  t.integer  "days_lost",   default: 0
  t.integer  "missed_days",   default: 0
  t.integer  "current_level"
  t.datetime "created_at",                null: false
  t.datetime "updated_at",                null: false
end

add_index "levels", ["habit_id"], name: "index_levels_on_habit_id"

The Gist of it: http://ift.tt/1PISTF0

NoMethodError in Blog::PostsController#create

Note: Rails newb here.

So, I recently created a Rails app with mongoid gem for use of MongoDB. I have a namespace route of :blog with a nest of resource of posts

Routes.rb:

Rails.application.routes.draw do
  namespace :blog do
    resources :posts
  end
end

The error comes from app/controllers/blog/post_controller.rb:

Class Blog::PostController < ApplicationController
  def create
   @post = Post.new(post_params)

  if @post.save
    redirect_to @post
  else
    render 'new'
  end
 end
end

I also have a 'post' model that comes with a title and body:

Class Post
 include Mongoid::Document
 field :title, type: String
 field :body, type: String
end

In new.html.erb:

<h1>Rails Sample Blog</h1>

<%= form_for :post, url: blog_post_path do |f| %>
  <div><%= f.label :title %><%= f.text_field :title %></div>
  <div><%= f.label :body %><%= f.text_area :body %></div>
<% end %>

Is there something I left out that I didn't catch? It's slowly haunting me.